Finding Relationship

[Murali4u]

Guys,
This may sound silly, but i dont know this could be achieved in datastage or through simply query, dont go for any stored procedures.
Let me describe with simple example.

I have a table with rows
A B
1 2
2 3

If we look into the relationship of the element B with element A for the value as '3'

The data should be represented as
1 2
1 3
Since 3 has the relationship with the top element as '2' and 2 has 1 so the relationship with 2 and 3 has the top level element as 1 only.
Another Ex:

A B
1 2
2 3
4 5
2 6

Transformed to :

1 2
1 3
4 5
1 6

Thanks in Advance..

[DSguru2B]

This is where recursive sql comes in. If your source is DB2, look into the 'WITH' clause and in oracle its 'connect_by_prior'.

[Murali4u]

[quote="DSguru2B"]This is where recursive sql comes in. If your source is DB2, look into the 'WITH' clause and in oracle its 'connect_by_prior'. ...[/quote]

Thanks Dsguru. when i used "connect by prior" it works fine.