Hi All,
Can someone pls shed some light around how to accomplish the below logic in Datastage.
I have an input file with the below values
Input :
---------
P1
P2
P3
I have another DB2 Table where i will have record with P1 value . The input record with value P1 is combined to P2 & P2 is combined to P3 & P3 is combined to P4 in the Db2 table (as shown in the below example)
Input fields
P : P1
-------------
P1 P2
P2 P3
P3 P4
The resultant output I am expecting is as below
O/P
-------------
P1 P4
Any ideas on how to accomplish this is appreciated.
Thanks
Synsog
Loop in Db2 Stage/Any alternate approach with DS stages
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray - Yes that s correct.
In the Table, record with P1 is combined to P2 (making P2 the latest value corresponding to P1 value). A few days later, P2 might get combined to P3 (making P3 the latest) & P3 to P4.
I am trying to find what is the latest 'P' value P1 is currently holding.
ie
P1=P2
P2=P3
P3=P4
so, what is the latest value of P1 ? (Output should be P4 in this case)
Thanks
Synsog
In the Table, record with P1 is combined to P2 (making P2 the latest value corresponding to P1 value). A few days later, P2 might get combined to P3 (making P3 the latest) & P3 to P4.
I am trying to find what is the latest 'P' value P1 is currently holding.
ie
P1=P2
P2=P3
P3=P4
so, what is the latest value of P1 ? (Output should be P4 in this case)
Thanks
Synsog
You can do this by using multiple left self-joins on the db2-table:
select table1.P,
case when tablen.P1 is not null then tablen.P1
...
when table3.P1 is not null then table3.P1
when table2.P1 is not null then table2.P1
else table1.P1 end P1
from table table1
left join table table2
on table1.P = table2.P1
left join table table3
on table2.P = table3.P1
...
left join table tablen
on table3.P = tablen.P1;
Can be done using DataStage-left-joins as well. Either way you need to know the maximum number of hierarchies within your data to get the correct result.
select table1.P,
case when tablen.P1 is not null then tablen.P1
...
when table3.P1 is not null then table3.P1
when table2.P1 is not null then table2.P1
else table1.P1 end P1
from table table1
left join table table2
on table1.P = table2.P1
left join table table3
on table2.P = table3.P1
...
left join table tablen
on table3.P = tablen.P1;
Can be done using DataStage-left-joins as well. Either way you need to know the maximum number of hierarchies within your data to get the correct result.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I would suggest using a Loop in a Transformer stage, using the SaveInputRecord() and GetSavedInputRecord() functions to manipulate the stack of records, as well as LastRecordInGroup() with a dummy constant argument column to exit from the loop.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.