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: 1281
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: 1281
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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