I am trying to remove duplicate subtree returned by "connect by prior" clause. I wish to check top level node of tree hierarchy, where user can enter child IDs which are already part of subtree. Take a look at following example:
SELECT * FROM (
With test_hierarchy as(
SELECT 'a' parent, 'b' child FROM dual UNION ALL
SELECT 'b','c' FROM dual UNION ALL
SELECT 'd','e' FROM dual UNION ALL
SELECT 'e','f' FROM dual UNION ALL
SELECT 'f','g' FROM dual UNION ALL
SELECT 'f','h' FROM dual)
SELECT
parent,
child,
CONNECT_BY_ROOT child AS init_child,
LEVEL,
CONNECT_BY_ISLEAF,
MAX(LEVEL) OVER(
PARTITION BY parent
) AS max_level
FROM
test_hierarchy
WHERE
CONNECT_BY_ISLEAF = 1
START WITH
child IN (
'c', 'b', 'e', 'f', 'h', 'g'
)
CONNECT BY
PRIOR parent = child);
This query returns result as:
P C I LEVEL CONNECT_BY_ISLEAF MAX_LEVEL
- - - ---------- ----------------- ----------
a b b 1 1 2
a b c 2 1 2
d e g 3 1 3
d e f 2 1 3
d e h 3 1 3
d e e 1 1 3
I wish to return only those top level nodes with level = max_level. I.e. my query should return result as:
P C I LEVEL CONNECT_BY_ISLEAF MAX_LEVEL
- - - ---------- ----------------- ----------
a b c 2 1 2
d e g 3 1 3
d e h 3 1 3
If I try to filter out results using WHERE clause as "WHERE level = max_level", Oracle complains:
ORA-01788: CONNECT BY clause required in this query block
01788. 00000 - "CONNECT BY clause required in this query block"
Please let me know if you have an idea on how to do it. Thanks,
Wrap your recursive query in another CTE and filter on that:
WITH
test_hierarchy AS (
SELECT 'a' parent, 'b' child FROM dual UNION ALL
SELECT 'b','c' FROM dual UNION ALL
SELECT 'd','e' FROM dual UNION ALL
SELECT 'e','f' FROM dual UNION ALL
SELECT 'f','g' FROM dual UNION ALL
SELECT 'f','h' FROM dual
),
recursion AS (
SELECT
parent,
child,
CONNECT_BY_ROOT child AS init_child,
LEVEL AS lvl,
CONNECT_BY_ISLEAF AS isleaf,
MAX(LEVEL) OVER(
PARTITION BY parent
) AS max_level
FROM
test_hierarchy
START WITH child IN ('c', 'b', 'e', 'f', 'h', 'g')
CONNECT BY PRIOR parent = child
)
SELECT *
FROM recursion
WHERE isleaf = 1 AND lvl = max_level