Skip to content
Advertisement

Oracle hierarchical queries data

The link gives a good example for overview on how to use Oracle hierarchical queries. I was trying to generate the below combination of data with the example table tab1 given in the link but struck for a while.Using Oracle version 12.2

PARENT_ID    CHILD_ID
--------    ---------
       1      2         # the root node and id combination (This is evident by using ROOT_ID,ID)
       1      3
       1      4
       .      .
       .      .
       2      5         # How to generate below combination in a select statement ont TAB1 table.
       2      6
       .      .
       .      .
       9     11
SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id
ORDER SIBLINGS BY id;

Output of the above select statement

        ID  PARENT_ID TREE                      LEVEL    ROOT_ID PATH                       LEAF
---------- ---------- -------------------- ---------- ---------- -------------------- ----------
         1            1                             1          1 1                             0
         2          1 ..2                           2          1 1-2                           0
         3          2 ....3                         3          1 1-2-3                         1
         4          2 ....4                         3          1 1-2-4                         0
         5          4 ......5                       4          1 1-2-4-5                       1
         6          4 ......6                       4          1 1-2-4-6                       1
         7          1 ..7                           2          1 1-7                           0
         8          7 ....8                         3          1 1-7-8                         1
         9          1 ..9                           2          1 1-9                           0
        10          9 ....10                        3          1 1-9-10                        0
        11         10 ......11                      4          1 1-9-10-11                     1
        12          9 ....12                        3          1 1-9-12                        1

Diagraph: enter image description here

Answer

The sql query to get the desired output are below:

Solution 1: using CTE

with h ( id, parent_id ) as (
       select id ,parent_id  from tab1
     ),
     r ( id  , parent_id, steps ) as (
       select id  , id    , 0    
       from   h
       union all
       select r.id, h.parent_id, steps + 1
       from   h join r
                on h.id = r.parent_id
     ) 
select    parent_id, id
from     r
where    parent_id != id
order by parent_id asc;

Solution 2: Using Oracle only connect by query. The CONNECT BY NOCYCLE clause can be used to not traverse cyclical hierarchies if any.

with hier_data as
(
select 
       connect_by_root id as parent_id
       ,id
    from tab1
connect by parent_id = prior id
order by parent_id,id
)
select * from hier_data 
where
parent_id != id;