Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Query to get the Hierarchy

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Tue Nov 17, 2009 12:14 pm    Post subject: DB2 Query to get the Hierarchy
Reply with quote

Hi,

This is my requirement.. I have 2 fields in my table Parent Field and Child field

Parent Field Child Feild
A B
B C
C D

A is the parent of B and B is the parent of C and C is the Parent of D. I want to find out the First Parent "A" . For an example

If i give "D" as in the where condition for Child Field I need to get the all related say D is child of C and C is the child of B and likewise,,,,,

Is there any query to get this done..... icon_confused.gif
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 17, 2009 2:10 pm    Post subject:
Reply with quote

yes, recursive SQL :
Code:
with CTE( lvl,par,child) as
(select 1, par,child from TableA where child = 'D'
union all
select c.lvl + 1, a2.par, a2.child from TableA a2, CTE c
where a2.child = c.par
  and c.lvl  < 99 )

select * from CTE


lvl is to make sure you don't loop endlessly when there would exist a row "A is child of A" or any other circular reference (a=>b, b=> a).
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Tue Nov 17, 2009 6:10 pm    Post subject:
Reply with quote

Hi GuyC

Thank you very much for the Query....That worked like a Charm....

Can you please explain the query,,,since im poor in this icon_confused.gif That would be help ful for me !!!!

Thank You again
Gayathri
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 17, 2009 6:40 pm    Post subject:
Reply with quote

1) It is using Common Table Expression : instead of using a subselect, you can now define a kind of temp table just before your select :
Code:
with temptablename(column1, column2) as (any select)

which you can then use in your select
Code:
select * from temptablename join etc....


2) Using this technique you can code "recursive sql"
you UNION ALL a table with itself :
- in the first leg of the union all you write a starting select :
Code:
select 1, par,child from TableA where child = 'D'

this would give the row 'D=>C'

-in the second leg you use the result of that starting select (which is now stored in temptablename , I used CTE ) and you join with something else :
Code:
select c.lvl + 1, a2.par, a2.child from TableA a2, CTE c
where a2.child = c.par
  and c.lvl  < 99

thus for the row (1,c,d) in CTE, you join with you tableA finding C=>B which gets stored in CTE (2,b,c)

And for this new row(s) of cte you repeat step 2, ... ad infinitum

In this example each repetition/recursion only adds one new row.
But suppose you go from parent to child.
then each repition adds several rows and the result set will grow exponentialy.

when using recursive SQL you better always built in a failsafe : a number/level which starts at 1 and adds 1 for each repetition , always write a where lvl < something in the where clause of the second leg of the union all.

hope this helps.
There are several examples of recursive sql and a several better explanations on the net , just google some.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us