DSXchange

One of my source table have some fields & i need to get hierarchical relationship beteen two of the columns. For example there are two fields in the table ID & SubID and they have relationships like
ID SUB_ID OtherFields
101 102 -----
102 103 -----
103 104 -----
104 105 -----
105 105 -----

And so on(This can go upto n level)

What i need is to get the output something like this :

ID SUB_ID D_Ultimate_ID
101 102 105
102 103 105
103 104 105
104 105 105
105 105 105

That is i need to get the last & ultimate ID associated with any parent ID.

What I have done is getting this through writing sql and having self join but this is valid if I would have fixed number of granular level, but as per my source data I am not sure of this granular level.
Could any body let me know about implementing this.

Any Help is appreciated.

Hi sourabhverma,

there is a solution to in SQL: recursive SQL.
It is a little complicated but with recursive SQL it will work with any number of levels.

I do not know the Netezza database you use but there is a good explanation of recusive SQL in the "DB2 SQL Cookbook". You wwill find it if you search for the name - and it is free.

best regards
Michael