| Author |
Message
|
| billybong |
 Posted: Tue Sep 20, 2005 12:34 am Post subject: Dynamic field/value pairs from database select[SOLVED] |
 |
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
Hi all!
I would like to create dynamic field/value pairs from a database select. If I write:
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(Environment.BatchJob[]);
WHILE I <= J DO
SET Environment.BatchJob[I].Param[] =
(SELECT o.PARAM_NAME, o.PARAM_VALUE
FROM Database.DBZTGO0 as o
WHERE o.BATCH_ID = Environment.BatchJob[I].BATCH_ID);
SET I = I + 1;
END WHILE;
I will get the resulting tree of
<Environment>
<BatchJob>
<Param>
<PARAM_NAME>
Days_to_keep_running
</PARAM_NAME>
<PARAM_VALUE>
53
</PARAM_VALUE>
</Param>
</BatchJob>
</Environment>
When what I really want is something like this:
<Environment>
<BatchJob>
<Param>
<Days_to_keep_running>
53
</Days_to_keep_running>
</Param>
</BatchJob>
</Environment>
Is there some way I can do this from the select case using ITEM or something. Or do I have to use a looping REFERENCE and CREATE_FIELD?
Last edited by billybong on Tue Sep 20, 2005 4:38 am; edited 1 time in total |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Tue Sep 20, 2005 12:57 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Try this
SELECT o.PARAM_NAME AS Days_to_keep_running....... |
|
| Back to top |
|
|
| billybong |
| Posted: Tue Sep 20, 2005 1:02 am Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
only problem is that I dont know the fieldname before doing the db select. I want the fieldname to be dynamic depending on the value of PARAMETER_NAME. The field will then be assigned the value of PARAMETER_VALUE.
Thanks for the quick reply though. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Tue Sep 20, 2005 1:18 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Ok i think i got what you want....
How do you know which column you are going to pick the value from in the first place ??? i.e SELECT o."____" how do u get the field name.. |
|
| Back to top |
|
|
| billybong |
| Posted: Tue Sep 20, 2005 1:31 am Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
I have two columns in my db for all kind of batch jobs.
The first column is named PARAMETER_NAME and specifies every parameter that should be set for the current batch job. The second column is named PARAMETER_VALUE and specifies the value of that parameter.
In this way, I can dynamically use which ever parameter I would like to without having it as a separate column in the database.
So, the DB looks something like this,
BATCH_JOB | PARAMETER_NAME | PARAMETER_VALUE |
------------------------------------------------------------------------------------
Give_horse_food | Days_to_keep_running | 53
Give_horse_food | Type_of_food | Carrots
Buy_milk | Times_a_day | 2
etc, etc |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Sep 20, 2005 3:06 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Try the following:
| Code: |
SET Environment.BatchJob[I].Param[] =
(SELECT o.PARAM_VALUE AS {o.PARAM_NAME}
FROM Database.DBZTGO0 as o
WHERE o.BATCH_ID = Environment.BatchJob[I].BATCH_ID); |
This will calculate the name for you.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| billybong |
| Posted: Tue Sep 20, 2005 4:37 am Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
Thanks a lot!
I thought it would be quite simple since it seems as a thing one often would like to do. |
|
| Back to top |
|
|
|
|
