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.
Hierarchical Logic implementation
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 74
- Joined: Thu Jan 05, 2006 2:07 am
Hierarchical Logic implementation
Thanks,
Sourabh Verma
Sourabh Verma
Re: Hierarchical Logic implementation
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
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