Skip to Main Content
  • Questions
  • Hierarchical CONNECT BY NOCYCLE Issue

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sagar.

Asked: February 03, 2021 - 12:22 pm UTC

Last updated: February 05, 2021 - 1:53 pm UTC

Version: 19.0.0.0.0

Viewed 100+ times

You Asked

Hi

In the shared livesql link contains re-producible test case.

I'm interested to list all the parent entities for a given child entity.

Below is the output I get when there is no cyclical loop condition:
SELECT child_entity AS child, parent_entity AS Parent, level, CONNECT_BY_ISCYCLE AS IsLoop, SYS_CONNECT_BY_PATH(child_entity,'\') AS Path 
FROM entities 
START WITH child_entity = 'abet' 
CONNECT BY NOCYCLE child_entity = PRIOR parent_entity 
ORDER SIBLINGS BY child_entity;

CHILD   PARENT  LEVEL   ISLOOP  PATH
------  ------  -----   ------  ----------------
abet abe  1  0  \abet
abe  ab  2  0  \abet\abe
ab  a  3  0  \abet\abe\ab
a  null 4  0  \abet\abe\ab\a
ab  c  3  0  \abet\abe\ab
abe  d  2  0  \abet\abe
abet b  1  0  \abet
b     null  2  0  \abet\b               

8 rows selected. 


Now when I introduce cyclical loop condition, the output is like below.
Duplicate last 2 rows can been seen in output.
insert into entities values('abet','a',900);
commit;

SELECT child_entity AS child, parent_entity AS Parent, level, CONNECT_BY_ISCYCLE AS IsLoop, SYS_CONNECT_BY_PATH(child_entity,'\') AS Path 
FROM entities 
START WITH child_entity = 'abet' 
CONNECT BY NOCYCLE child_entity = PRIOR parent_entity 
ORDER SIBLINGS BY child_entity;

CHILD   PARENT  LEVEL   ISLOOP  PATH
------  ------  -----   ------  ----------------
abet abe  1  0  \abet
abe  ab  2  0  \abet\abe
ab  a  3  0  \abet\abe\ab
a       abet    4       1       \abet\abe\ab\a
abet    b       5       0  \abet\abe\ab\a\abet
b  null 6  0  \abet\abe\ab\a\abet\b
a  null 4  0  \abet\abe\ab\a
ab  c  3  0  \abet\abe\ab
abe  d  2  0  \abet\abe
abet b  1  0  \abet
b  null 2  0  \abet\b
               
11 rows selected. 


I'm expecting to produce an output like below. How to remove the duplicate extra two rows coming in above SQL result.
Please share inputs on how I can achieve the same.
CHILD   PARENT  LEVEL   ISLOOP  PATH
------  ------  -----   ------  ----------------
abet abe  1  0  \abet
abe  ab  2  0  \abet\abe
ab  a  3  0  \abet\abe\ab
a       abet    4       1       \abet\abe\ab\a
abet    b       5       0  \abet\abe\ab\a\abet
b  null 6  0  \abet\abe\ab\a\abet\b
a  null 4  0  \abet\abe\ab\a
ab  c  3  0  \abet\abe\ab
abe  d  2  0  \abet\abe


Thanks..

with LiveSQL Test Case:

and we said...

The challenge is these rows:

abet b    1  0  \abet
b    null 2  0  \abet\b


form a new tree; the start with clause picks up the first of these (child_entity = 'abet'). So it's not a cycle with the other hierarchy; cycle detection won't help you here.

You need to change the start with clause to stop it picking up both rows for child_entity = 'abet'.

An example of how you could do this for these data is:

select child_entity as child, parent_entity as parent, level, 
       connect_by_iscycle as isloop, 
       sys_connect_by_path(child_entity,'\') as path 
from   entities e
start with child_entity = 'abet' 
and not exists (
  select * from entities s
  where  e.child_entity = s.child_entity
  and    e.parent_entity > s.parent_entity 
)
connect by nocycle child_entity = prior parent_entity 
order siblings by child_entity;

CHILD    PARENT    LEVEL    ISLOOP    PATH                    
abet     abe              1         0 \abet                    
abe      ab               2         0 \abet\abe                
ab       a                3         0 \abet\abe\ab             
a        abet             4         1 \abet\abe\ab\a           
abet     b                5         0 \abet\abe\ab\a\abet      
b        <null>           6         0 \abet\abe\ab\a\abet\b    
a        <null>           4         0 \abet\abe\ab\a           
ab       c                3         0 \abet\abe\ab             
abe      d                2         0 \abet\abe  


You'll need to tread carefully here: abet -> b does not form a hierarchy with rest of the rows, so if you flip the inequality in the not exists you get:

select child_entity as child, parent_entity as parent, level, 
       connect_by_iscycle as isloop, 
       sys_connect_by_path(child_entity,'\') as path 
from   entities e
start with child_entity = 'abet' 
and not exists (
  select * from entities s
  where  e.child_entity = s.child_entity
  and    e.parent_entity < s.parent_entity 
)
connect by nocycle child_entity = prior parent_entity 
order siblings by child_entity;

CHILD    PARENT    LEVEL    ISLOOP    PATH      
abet     b                1         0 \abet      
b        <null>           2         0 \abet\b  


Coming up with a general case exclusion that avoids this problem will be tricky.

Rating

  (3 ratings)

Comments

Thanks

Sagar, February 03, 2021 - 8:13 pm UTC

Thanks for the above feedback Chris.


ORDER BY SIBLINGS result in ORA-00907

Sagar, February 04, 2021 - 7:23 am UTC

Hi Chris, Connor

When trying to fetch details for these parent entities in another table, I get "ORA-00907: missing right parenthesis" exception when using this same hierarchical query as above within either IN or EXISTS clause.

Is there a fix for this issue?

SELECT name
FROM master
WHERE entity IN (
      SELECT parent_entity
      FROM entities 
      START WITH child_entity = 'abet' 
      CONNECT BY NOCYCLE child_entity = PRIOR parent_entity 
      ORDER SIBLINGS BY child_entity
);

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"


Same result when using EXISTS clause instead of IN clause.

Thanks..
Chris Saxon
February 04, 2021 - 9:45 am UTC

Remove the order by from the subquery.

Thanks

Sagar, February 04, 2021 - 5:17 pm UTC

Used WITH sub-query to process the SQL with ORDER BY siblings clause. Worked fine.

Thanks Chris.
Chris Saxon
February 05, 2021 - 1:53 pm UTC

Great

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.