Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum 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: 44
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: 2309
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: 10637
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: 2309
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
Auryn

New User


Joined: 11 Jan 2006
Posts: 66
Location: Lower Saxony (DE)

PostPosted: Wed Nov 06, 2019 2:58 am    Post subject: Common Table Expressions
Reply with quote

Got what you're trying.
Recursive queries only work with
Quote:
Common Table Expressions

Sorry, I don't have one sample here with me.
But maybe tomorrow I can show you one...
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 66
Location: Lower Saxony (DE)

PostPosted: Wed Nov 06, 2019 2:44 pm    Post subject: Try this...
Reply with quote

Hi and good morning,

entered your sample data in my experimental table "SELF_REF_TEST" which has the columns
    MY_ID INTEGER NOT NULLable
    PARENT_ID INTEGER NULLable
    TEXT VARCHAR(255) NOT NULLable
    and some more that are not relevant in this context

Try the following query - where »WITH struktur« describes the common table expression.
Code:
    WITH struktur
         (                     gen_level
                           ,   id_path
                           ,   start_id
                           ,   my_id
                           ,   parent_id
                           ,   text           )
      AS (          SELECT     0
                           ,   VARCHAR(DIGITS(my_id), 999)
                           ,   my_id                  AS start_id
                           ,   my_id
                           ,   parent_id
                           ,   text
                      FROM     self_ref_test
--                   WHERE     parent_id              IS NULL   -- Alle Strukur-Listen für alle Root-Elemente (ohne Parent)
--                   WHERE     parent_id               = 0      -- Alle Strukur-Listen für alle eigentl. Root-Elemente (ohne Parent)
                     WHERE     my_id                   = 59083  -- Strukur-Listen nach unten ab diesem Element
          UNION ALL
                    SELECT     str.gen_level + 1
                           ,   VARCHAR(    str.id_path
                                       ||DIGITS(srt.my_id)
                                     ,  999)
                           ,   str.start_id
                           ,   srt.my_id
                           ,   srt.parent_id
                           ,   srt.text
                      FROM             self_ref_test
                                                         srt
                           INNER JOIN  struktur          str
                        ON     srt.parent_id           = str.my_id
         )
   
    SELECT      gen_level
              , my_id
              , parent_id
              , start_id
              , id_path
              , text
      FROM      struktur
     ORDER BY   id_path
--   ORDER BY   gen_level
--            , parent_id
--            , my_id
      WITH UR
    ;

You can see the second part of the union clause in it references to itself.
The VARCHAR(DIGITS(...)) clause is necessary to convert binary integer values as CHAR to concat 'em.
GEN_LEVEL indicates the hierarchy level
START_ID means the ID of the root.
ID_PATH represents something similar to you desired result, but with my IDs.

The result is
Code:
GEN_LEVEL  MY_ID  PARENT_ID  START_ID  ID_PATH                                   TEXT
---------  -----  ---------  --------  ----------------------------------------  -------
        0  59083         {}     59083  0000059083                                1-One
        1  59084      59083     59083  00000590830000059084                      2-Two
        2  59085      59084     59083  000005908300000590840000059085            3-Three
        2  59086      59084     59083  000005908300000590840000059086            4-Four
        3  59087      59086     59083  0000059083000005908400000590860000059087  5-Five

Hope this is helpful for you
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 Dynamically split large DSN list into... Ghellar CLIST & REXX 14 Tue Sep 17, 2019 8:24 pm
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

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