Recursive lookups

atulgoel · Post by **atulgoel** » Mon Sep 07, 2009 1:20 am

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

ArndW · Post by **ArndW** » Mon Sep 07, 2009 1:35 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Sep 07, 2009 2:51 am

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.

laknar · Post by **laknar** » Mon Sep 07, 2009 6:57 am

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).

algfr · Post by **algfr** » Mon Sep 07, 2009 10:18 am

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.

Sreenivasulu · Post by **Sreenivasulu** » Mon Sep 07, 2009 12:35 pm

Hi

Store this in a Oracle table and you can use the CONNECT BY clause to
get this recursive work

Regards
Sreeni

DSguru2B · Post by **DSguru2B** » Mon Sep 07, 2009 3:50 pm

Or in DB2 use WITH command. Lookup the syntax on google.
A single sql query would suffice, no need for a sql stored proc.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 07, 2009 5:31 pm

Or in UniVerse use WITHIN. (But this is messy from parallel jobs.)