Loop in Db2 Stage/Any alternate approach with DS stages

[synsog]

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

[ray.wurlod]

So, to put it another way, you want to traverse a hierarchy and return the start and end points?

[synsog]

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

[BI-RMA]

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.

[synsog]

Thanks Roland,

I am not sure whether i understood the below logic. Can you pls tell me what Table n...3,2,1 etc is ?

Also, can you pls shed some light on how we can do it using Datastage self joins ?

Thanks
Synsog

[ray.wurlod]

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.