...and I know it's because I have a circular reference in the data. I'm wondering if there is a way around it. This is my next step from this topic:
407391
I've implemented the query I need to get a generic heirarchical result but here's the problem.
This is an attempt at a database conversion for a system redesign. Unfortunately the way it's currently done is this:
-Orphan records have nothing in the parent_id field
-Child records have the parent_id in the parent_id field
-ultimate parent records (parents that are not themselves a child of anything, or top level parents) have their own id in the parent_id field.
Now, this is the query I've got working:
SELECT LEVEL
, CONNECT_BY_ROOT PARENT_PKG_ID "ROOT"
, CONNECT_BY_ISLEAF "LEAF"
, CONNECT_BY_ISCYCLE "CYCLE"
, PKG_ID
, DESCRIPTION
, PARENT_PKG_ID
, SYS_CONNECT_BY_PATH(PKG_ID, '/') "PATH"
FROM CATALOG
CONNECT BY NOCYCLE PRIOR PKG_ID = PARENT_PKG_ID
ORDER SIBLINGS BY pkg_id
What comes out is:
LEVEL ROOT LEAF CYCLE PKG_ID DESCRIPTION PARENT_PKG_ID PATH
1 C150552629 1 1 C150552629 TRUCK CARGO C150552629 /C150552629
2 C150552629 1 0 C150711392 TRAILER, MEDIUM, TOWED C150552629 /C150552629/C150711392
1 C150552629 1 0 C150711392 TRAILER, MEDIUM, TOWED C150552629 /C150711392
So that 2nd entry for the trailer at level 1 is popping up and should not be there. I'm guessing this is a result of the parent (self) reference. If I clear all the ultimate parent values, the problem clears itself up and I can use a normal hierarchical query but there is a lingering overhead question regarding trasmission of data back and forth for backward compatability and it will most likely be argued to keep the current scheme as is.
Is there a way to get rid of the cyclic duplicates? (for lack of a better word)
Thanks.