Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Timur.

Asked: June 02, 2008 - 6:36 pm UTC

Last updated: April 30, 2009 - 8:43 am UTC

Version: 10gR2

Viewed 10K+ times! This question is

You Asked

Tom,

Here's what I have:

CREATE TABLE BRANCHES(PARENT_NODE VARCHAR(10), CHILD_NODE VARCHAR(10))
    
insert into BRANCHES values (' ','LEVEL1');    
insert into BRANCHES values ('LEVEL1','LEVEL2'); 
insert into BRANCHES values ('LEVEL2','LEVEL3.1');
insert into BRANCHES values ('LEVEL2','LEVEL3.2');
insert into BRANCHES values ('LEVEL2','LEVEL3.3');
insert into BRANCHES values ('LEVEL3.1','LEVEL4.1');
insert into BRANCHES values ('LEVEL3.1','LEVEL4.2');
insert into BRANCHES values ('LEVEL3.3','LEVEL4.3');
insert into BRANCHES values ('LEVEL3.3','LEVEL4.4');
insert into BRANCHES values ('LEVEL4.1','LEVEL5.1');
insert into BRANCHES values ('LEVEL4.1','LEVEL5.2');
insert into BRANCHES values ('LEVEL4.2','LEVEL5.3');
insert into BRANCHES values ('LEVEL4.2','LEVEL5.4');
insert into BRANCHES values ('LEVEL4.3','LEVEL5.5');
insert into BRANCHES values ('LEVEL4.3','LEVEL5.6');
insert into BRANCHES values ('LEVEL4.4','LEVEL5.7');
insert into BRANCHES values ('LEVEL4.4','LEVEL5.8');
insert into BRANCHES values ('LEVEL5.2','LEVEL6.1');
insert into BRANCHES values ('LEVEL5.2','LEVEL6.2');
insert into BRANCHES values ('LEVEL5.3','LEVEL6.3');
insert into BRANCHES values ('LEVEL5.3','LEVEL6.4');
insert into BRANCHES values ('LEVEL5.4','LEVEL6.5');


By using SYS_CONNECT_BY_PATH I can generate all possible tree traversing paths:

select SYS_CONNECT_BY_PATH(CHILD_NODE, ' - ')
    from BRANCHES
   start with PARENT_NODE = ' '
   connect by prior CHILD_NODE = PARENT_NODE

SYS_CONNECT_BY_PATH(CHILD_NODE,'-')
--------------------------------------------------------------------------------
 - LEVEL1
 - LEVEL1 - LEVEL2
 - LEVEL1 - LEVEL2 - LEVEL3.1
 - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1
 - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.1
 - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2
 - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2 - LEVEL6.1
 - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2 - LEVEL6.2
etc.


But what I am trying to get is this:

ROOT  L1       L2       L3         L4        L5         L6
----- -------  -------  ---------  --------- ---------- ----------
      LEVEL1                                            
      LEVEL1   LEVEL2                                   
      LEVEL1   LEVEL2   LEVEL3.1                        
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.1             
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.1  LEVEL5.1   
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.1  LEVEL5.2   
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.1  LEVEL5.2   LEVEL6.1
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.1  LEVEL5.2   LEVEL6.2
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2             
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2  LEVEL5.3   
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2  LEVEL5.3   LEVEL6.3
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2  LEVEL5.3   LEVEL6.4
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2  LEVEL5.4   
      LEVEL1   LEVEL2   LEVEL3.1   LEVEL4.2  LEVEL5.4   LEVEL6.5
      LEVEL1   LEVEL2   LEVEL3.2                        
      LEVEL1   LEVEL2   LEVEL3.3                        
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.3             
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.3  LEVEL5.5   
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.3  LEVEL5.6   
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.4             
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.4  LEVEL5.7   
      LEVEL1   LEVEL2   LEVEL3.3   LEVEL4.4  LEVEL5.8


Any advice on how this can be done? Thank you.

and Tom said...

is six the maximum?

a sql query needs to have "N columns" where N is absolutely known at PARSE time.

If you just need six, then......

substr, just substr the sys connect by path.


ops$tkyte%ORA11GR1> select substr( scbp, instr(scbp,'-',1,1)+1, instr(scbp,'-',1,2)-instr(scbp,'-',1,1)-1) l1,
  2         substr( scbp, instr(scbp,'-',1,2)+1, instr(scbp,'-',1,3)-instr(scbp,'-',1,2)-1) l2,
  3         substr( scbp, instr(scbp,'-',1,3)+1, instr(scbp,'-',1,4)-instr(scbp,'-',1,3)-1) l3,
  4         substr( scbp, instr(scbp,'-',1,4)+1, instr(scbp,'-',1,5)-instr(scbp,'-',1,4)-1) l4,
  5         substr( scbp, instr(scbp,'-',1,5)+1, instr(scbp,'-',1,6)-instr(scbp,'-',1,5)-1) l5,
  6         substr( scbp, instr(scbp,'-',1,6)+1, instr(scbp,'-',1,7)-instr(scbp,'-',1,6)-1) l6
  7    from (
  8  select SYS_CONNECT_BY_PATH(CHILD_NODE, '-') || '-' scbp
  9      from BRANCHES
 10     start with PARENT_NODE = ' '
 11     connect by prior CHILD_NODE = PARENT_NODE
 12         )
 13  /

L1         L2         L3         L4         L5         L6
---------- ---------- ---------- ---------- ---------- ----------
LEVEL1
LEVEL1     LEVEL2
LEVEL1     LEVEL2     LEVEL3.1
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.1
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.1   LEVEL5.1
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.1   LEVEL5.2
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.1   LEVEL5.2   LEVEL6.1
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.1   LEVEL5.2   LEVEL6.2
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2   LEVEL5.3
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2   LEVEL5.3   LEVEL6.3
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2   LEVEL5.3   LEVEL6.4
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2   LEVEL5.4
LEVEL1     LEVEL2     LEVEL3.1   LEVEL4.2   LEVEL5.4   LEVEL6.5
LEVEL1     LEVEL2     LEVEL3.2
LEVEL1     LEVEL2     LEVEL3.3
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.3
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.3   LEVEL5.5
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.3   LEVEL5.6
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.4
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.4   LEVEL5.7
LEVEL1     LEVEL2     LEVEL3.3   LEVEL4.4   LEVEL5.8

22 rows selected.




Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

or use regexp_substr

Georg, June 03, 2008 - 5:23 pm UTC

On 10g you can also use regexp_substr:

select substr(regexp_substr(scbp, '-[^-]*', 1, 1),2) as l1,
substr(regexp_substr(scbp, '-[^-]*', 1, 2),2) as l2,
substr(regexp_substr(scbp, '-[^-]*', 1, 3),2) as l3,
substr(regexp_substr(scbp, '-[^-]*', 1, 4),2) as l4,
substr(regexp_substr(scbp, '-[^-]*', 1, 5),2) as l5,
substr(regexp_substr(scbp, '-[^-]*', 1, 6),2) as l6
from (
select sys_connect_by_path(child_node, '-') scbp
from branches
start with parent_node = ' '
connect by prior child_node = parent_node
)


Good stuff

Timur Kanaatov, June 03, 2008 - 11:26 pm UTC

This was very helpful - I am able to replace 1400 lines of legacy SQL with just a few statements now.

Want to know how to do this

Vijay, February 27, 2009 - 6:29 pm UTC

I want to filter some of the data in the hirachical data set not all... but only few how can i do it?
Tom Kyte
March 03, 2009 - 9:53 am UTC

where???


very vague

Eugene, April 29, 2009 - 8:27 pm UTC

Tom,
I am looking at using sys_connect_by_path to pull all children for a given table. I can use the following:
select
parents.owner || '.' || parents.table_name parent_table,
child.owner || '.' || child.table_name child_table,
child.r_constraint_name
from
dba_constraints child,
dba_constraints parents
where
child.r_constraint_name = parents.constraint_name
and child.r_owner = parents.owner
and parents.table_name = 'SALES';

but it will gives me one level of it. Each of the children has its own children. Is there a way to find ALL children all the way down from a particular table?

Thanks,
Eugene
Tom Kyte
April 30, 2009 - 8:43 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:661009003696
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.