ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Hierarchical table - given one element of a set, how to retrieve the entire set

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hierarchical table - given one element of a set, how to retrieve the entire set

    I am trying to solve the exact same issue as the person who created this question at stackOverflow regarding hierarchical tables (ie. self referencing tables). The examples below are from that article. So, if 'D' is the given element then the Ids that should be returned are 1,2,3,4,5,6 because they are all related.

    I need to return the entire tree (ie. list of elements) to which the single element given belongs

    The solution given uses Oracle sql, and I am having trouble converting it to iSeries sql since some of the keywords are Oracle specific

    Any help is greatly appreciated

    Code:
         ID        Parent_ID   Name
        1         (null)      A
        2         1           B
        3         1           C
        4         2           D
        5         3           E
        6         5           F
        7         (null)      G
        8         (null)      H
    Code:
      
     with
       t as (
          select id
          from your_table
          where name = 'D'  -- your starting point
       )
    select id
    from (
       select id, parent_id from your_table
       where parent_id is not null
       union all
       select parent_id, id from your_table
       where parent_id is not null
       union all
       select id, null from t
    )
    start with parent_id is null
    connect by nocycle prior id = parent_id
    TIA
    Walt
    Last edited by wegrace; August 29, 2018, 03:59 PM.

  • #2
    Anyone?

    Bueller?

    Comment


    • #3
      Well, I finally had some time to really dig into this...

      I made the assumption that those keywords didn't exist for us, so I was trying to translate that sql to a CTE in the form that I've used for years, and failing miserably.

      Of course I found out I was wrong about the keywords once I went back to the sql reference red book and started reading a bit; I need to check that book more often.

      That sql I posted works as is, and it's fast. 0-)

      Edit: Except for the null keyword on the select statement, i had to use nullif()

      Walt
      Last edited by wegrace; September 5, 2018, 08:46 AM.

      Comment

      Working...
      X