getting Hierarchical data

Sandeepch19 · Post by **Sandeepch19** » Tue Jul 26, 2011 12:14 pm

Hi,
I wanted to know , how we can implement the job to retrieve the hierarchical data. The exampale would is as below

lets say we have a table a and three columns id1,name,id2. I need to get the all the parents of id1. for ex: if i have the below values in id1 and id2 field
id1 id2
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10
10

i need to get all the 10 records in the output

chulett · Post by **chulett** » Tue Jul 26, 2011 2:08 pm

What database? For Oracle, you have CONNECT BY and I imagine other products have something similar.

Sandeepch19 · Post by **Sandeepch19** » Wed Jul 27, 2011 2:38 am

yes it is oracle, but my question is whether we can implement this using datastage or not.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 27, 2011 3:32 am

Not in a single pass.

chulett · Post by **chulett** » Wed Jul 27, 2011 7:26 am

You can certainly implement the SQL in DataStage.

chulett · Post by **chulett** » Wed Jul 27, 2011 7:33 am

Otherwise, perhaps the judicious use of a hashed file would work or perhaps a lookup with multi-row result set enabled. It's hard for me to tell from your example since all of the numbers run sequentially. Those "10 records" you need in your output come from how many input records - 1? 10?

Spell out for us in words the process each input row must go through to generate the proper output(s).

DSXchange

getting Hierarchical data

getting Hierarchical data

retreiving hierarchy data using datastage