Page 1 of 1
Recursive lookups
Posted: Mon Sep 07, 2009 1:20 am
by atulgoel
Hi,
I have a data like;
100:200
200:300
300:400
500:600
600:700
900:1000
1000:1100
Input data shows that 200 is linked to 100, 300 is linked to 200 and also 200 is linked to 100 so 300 is linked to 100. similarly, 400 is also linked to 100 because 300 is linked to 200 and 200 is linked to 100.
And the required output is:
200:100
300:100
400:100
600:500
700:500
1000:900
1100:900
Can i use recursive lookup to achieve this and if yes, then how?
Thanks in Advance.
Atul
Posted: Mon Sep 07, 2009 1:35 am
by ArndW
DataStage does not have a capacity for recursive lookups the way you explained. It is possible to have several consecutive lookups to the same source located after each other in order to emulate this functionality but you would need to put sufficient lookups to guarantee that you would find the "root".
Depending upon where your source data is stored (i.e. in a database), you might be able to resolve the lookups using an appropriate SQL query.
Posted: Mon Sep 07, 2009 2:51 am
by Sainath.Srinivasan
Is that only the structure you need or do you have more attributes to work with?
You can do that by running through the data 2 times.
First time you can load the data - as it is - into a table.
Second time you can use it as a lookup with flattened data (using something like level of Oracle) to locate the root node.
Posted: Mon Sep 07, 2009 6:57 am
by laknar
Why not define Reference Table for all Combinations.
Key Value
200 100
300 100
400 100
600 500
700 500
1000 900
1100 900
Field(InputData,':',2)--Output of this to be used to Lookup in Reference Table(Key Column).
Posted: Mon Sep 07, 2009 10:18 am
by algfr
Where does the data come from ?
If you want a powerful solution and you're using a DB, I strongly recommend to create a stored procedure function that will handle any possible depth.
From there call the stored procedure in a SQL statement in Datastage.
Also, this is the most efficient method to me.
Posted: Mon Sep 07, 2009 12:35 pm
by Sreenivasulu
Hi
Store this in a Oracle table and you can use the CONNECT BY clause to
get this recursive work
Regards
Sreeni
Posted: Mon Sep 07, 2009 3:50 pm
by DSguru2B
Or in DB2 use WITH command. Lookup the syntax on google.
A single sql query would suffice, no need for a sql stored proc.
Posted: Mon Sep 07, 2009 5:31 pm
by ray.wurlod
Or in UniVerse use WITHIN. (But this is messy from parallel jobs.)