I have the following table in an oracle:
P_C_REL (Child , Parent ,Ownership)
Child | Parent | Ownership |
---|---|---|
41203134 | 10151091 | 100 |
10250181 | 40001104 | 30 |
40856701 | 40109963 | 100 |
10159060 | 40001104 | 100 |
41328372 | 10250181 | 100 |
40001471 | 10159060 | 100 |
10250181 | 40001471 | 70 |
10151091 | 40856701 | 100 |
40001104 | 10351678 | 100 |
One child can have multiple parents and each different can lead to different path till final parent.
I need output of child, RecursiveParents, Path.
Assuming 41203134 and 41328372 are my base child for sampling purpose. I don't want to paramterize it. Child will come from different table.
Desired result #1 : get all the paths
Child | Parents | Path |
---|---|---|
41203134 | 41203134,10151091,40856701,40109963 | Path1 |
41328372 | 41328372,10250181,40001104,10351678 | Path1 |
41328372 | 41328372,10250181,40001471,10159060,40001104,10351678 | Path2 |
Desired result #2 : get the path which having highest ownership
Child | Parents | Path |
---|---|---|
41203134 | 41203134,10151091,40856701,40109963 | Path1 |
41328372 | 41328372,10250181,40001471,10159060,40001104,10351678 | Path2 |
How can I do this in Oracle 19c?
This is my query:
WITH REL_TABLE(child, parent) AS
(
SELECT
child, parent
FROM
P_C_REL p
WHERE
parent NOT IN (SELECT child
FROM P_C_REL)
UNION ALL
SELECT
p.child, a.parent
FROM
REL_TABLE a
JOIN
P_C_REL p ON p.parent = a.child
)
SELECT *
FROM REL_TABLE
WHERE child IN (SELECT child FROM childTable)
I get this error:
ORA-32044: cycle detected while executing recursive WITH query
Use a hierarchical query:
SELECT CONNECT_BY_ROOT child AS child,
SUBSTR(SYS_CONNECT_BY_PATH(child, ',' ), 2) || ',' || parent AS path
FROM p_c_rel
WHERE CONNECT_BY_ISLEAF = 1
START WITH child IN (41203134, 41328372)
CONNECT BY child = PRIOR parent
Which, for the sample data:
CREATE TABLE P_C_REL (Child , Parent ,Ownership) AS
SELECT 41203134, 10151091, 100 FROM DUAL UNION ALL
SELECT 10250181, 40001104, 30 FROM DUAL UNION ALL
SELECT 40856701, 40109963, 100 FROM DUAL UNION ALL
SELECT 10159060, 40001104, 100 FROM DUAL UNION ALL
SELECT 41328372, 10250181, 100 FROM DUAL UNION ALL
SELECT 40001471, 10159060, 100 FROM DUAL UNION ALL
SELECT 10250181, 40001471, 70 FROM DUAL UNION ALL
SELECT 10151091, 40856701, 100 FROM DUAL UNION ALL
SELECT 40001104, 10351678, 100 FROM DUAL;
Outputs:
CHILD | PATH |
---|---|
41203134 | 41203134,10151091,40856701,40109963 |
41328372 | 41328372,10250181,40001104,10351678 |
41328372 | 41328372,10250181,40001471,10159060,40001104,10351678 |
Or, a recursive query:
WITH path (root, path, parent, ownership) AS (
SELECT child, child || ',' || parent, parent, ownership
FROM p_c_rel
WHERE child IN (41203134, 41328372)
UNION ALL
SELECT p.root, p.path || ',' || r.parent, r.parent, p.ownership + r.ownership
FROM path p
INNER JOIN p_c_rel r
ON p.parent = r.child
)
SELECT root, path, ownership
FROM path
ORDER BY DENSE_RANK() OVER (PARTITION BY root ORDER BY ownership DESC)
FETCH FIRST ROW WITH TIES;
Which outputs:
ROOT | PATH | OWNERSHIP |
---|---|---|
41203134 | 41203134,10151091,40856701,40109963 | 300 |
41328372 | 41328372,10250181,40001471,10159060,40001104,10351678 | 470 |