Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
recursive SQL to generate list

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

New User


Joined: 31 Oct 2006
Posts: 38
Location: brisbane

PostPosted: Sun Apr 07, 2019 8:38 am    Post subject: recursive SQL to generate list
Reply with quote

for the following table:

P C

1 2
2 3
2 4
4 5

I need to generate the set of all paths (Parent to Child) starting at (say) 1. this would yield :

result (in one column only)

"1-2"
"1-2-3"
"1-2-4"
"1-2-4-5"


I can do this in oracle, but can't see a way in Db2 (z/OS) because of restrictions on back-referencing anchor rows.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2129
Location: NY,USA

PostPosted: Tue Apr 09, 2019 7:34 pm    Post subject:
Reply with quote

Please explain, how you got "1-2-4-5"?
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10586
Location: italy

PostPosted: Tue Apr 09, 2019 10:59 pm    Post subject:
Reply with quote

first row 1 ==> 2

third row 2 ==> 4

last row 4 ==> 5
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2129
Location: NY,USA

PostPosted: Wed Apr 10, 2019 12:24 am    Post subject:
Reply with quote

Thanks Enrico.

Quote:
but can't see a way in Db2 (z/OS) because of restrictions on back-referencing anchor rows.

Did you think or tried any sql using OLAP- RANK function or Lag or Lead to compare next vs previous row(s)?
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 Silly question maybe - REPLACE member... Cloink TSO/ISPF 15 Thu Oct 25, 2018 6:38 pm
No new posts Generate SQL query dynamically using ... vnktrrd DB2 7 Tue Aug 28, 2018 8:11 pm
No new posts COBOL - JSON GENERATE husni1972 COBOL Programming 1 Fri Aug 17, 2018 3:29 am
No new posts Generate and submit JCL and step dyna... vnktrrd DFSORT/ICETOOL 11 Wed Jul 25, 2018 11:31 pm
No new posts Utility to list Data Set Information Auryn JCL & VSAM 6 Tue Jul 17, 2018 5:03 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us