My table has a breadcrumb not unlike a filesystem to show page navigation. For example <level1> / <level2> / <level3>. I want to start with any the <level1> level and navigate to the child <level1> / <level2> breadcrumb. I don't care whether it goes breadth or depth first, I just need to follow the hierarchy so I can record the parent child items for another purpose.
My attempts only give me the first level and stop.
create table sc_hub_hierarchy_test (breadcrumb varchar2(1000),child_breadcrumb varchar2(1000),crumb_depth);
insert into sc_hub_hierarchy_test (breadcrumb,child_breadcrumb) values('Level1A','Level1A/Level2A',0);
insert into sc_hub_hierarchy_test (breadcrumb,child_breadcrumb) values('Level1B','Level1B/Level2A',0);
insert into sc_hub_hierarchy_test (breadcrumb,child_breadcrumb) values('Level1B','Level1B/Level2B',0);
insert into sc_hub_hierarchy_test (breadcrumb,child_breadcrumb) values('Level1A/Level2A','Level1A/Level2A/Level3A',2);
select breadcrumb
from sc_hub_hierarchy_test
start with crumb_depth=0
connect by breadcrumb=child_breadcrumb;
Also tried:
select breadcrumb
from sc_hub_hierarchy_test
start with crumb_depth=0
connect by prior breadcrumb=child_breadcrumb;
My results were like this below:
Level1A
Level1B
Level1B
Level1A
** Note, actual data used is different to simplify the question.
In my data, there is more than one root, so if the "start with" clause were changed to a "where" clause, multiple rows would be returned. There are more than one level.
I also tried removing the "start with" clause, and got more results. The behavior is as though it is applying the condition on the "start with" clause to each level.
Why did Larry Ellison decide to stop after step one?
You are missing the PRIOR keyword.
Your query should be like the following:
SQL> SELECT BREADCRUMB, LEVEL 2 FROM SC_HUB_HIERARCHY_TEST 3 START WITH CRUMB_DEPTH = 0 4 CONNECT BY BREADCRUMB = PRIOR CHILD_BREADCRUMB; BREADCRUMB LEVEL --------------- ---------- Level1A 1 Level1A/Level2A 2 Level1B 1 Level1B 1 SQL>
Cheers!!