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..
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.