Craig Stevensson Craig Stevensson - 3 months ago 12
SQL Question

Oracle Hierarchy with shared members (double parent members)

I'm working with hierarchical data in which there are some keys that has double or triple parents.

I have to write a hierarchical query to represent the hierarchy, but this double or triple parent keys must appear alone in the tree, displaying their childs is only allowed in the first key found in the three.

WITH HER(CHILD, PARENT) AS (
SELECT 'A' AS CHILD, null AS PARENT FROM DUAL UNION
SELECT 'B' AS CHILD, 'A' AS PARENT FROM DUAL UNION
SELECT 'C' AS CHILD, 'A' AS PARENT FROM DUAL UNION
SELECT 'D' AS CHILD, 'C' AS PARENT FROM DUAL UNION
SELECT 'E' AS CHILD, 'D' AS PARENT FROM DUAL UNION
SELECT 'F' AS CHILD, null AS PARENT FROM DUAL UNION
SELECT 'G' AS CHILD, 'F' AS PARENT FROM DUAL UNION
SELECT 'C' AS CHILD, 'G' AS PARENT FROM DUAL UNION --<<--- shared
SELECT 'H' AS CHILD, null AS PARENT FROM DUAL UNION
SELECT 'B' AS CHILD, 'H' AS PARENT FROM DUAL UNION --<<--- shared
SELECT 'X' AS CHILD, null AS PARENT FROM DUAL UNION
SELECT 'Y' AS CHILD, 'X' AS PARENT FROM DUAL UNION
SELECT 'Z' AS CHILD, 'Y' AS PARENT FROM DUAL UNION
SELECT 'C' AS CHILD, 'Z' AS PARENT FROM DUAL --<<--- shared
)
SELECT
LPAD(' ', 7*(LEVEL-1),' ')||CHILD||' - '||PARENT
FROM HER
START WITH PARENT IS NULL
CONNECT BY PRIOR CHILD = PARENT


;

The result from this query is this, but it's not exactly what I'm trying to get:

----------
A -
B - A
C - A
D - C
E - D
F -
G - F
C - G
*D - C*
*E - D*
H -
B - H
X -
Y - X
Z - Y
C - Z
*D - C*
*E - D*


Rows D-C and E-D should just be displayin in the first appearance of "C" key. So those I marked with "*" should not appear.

I know that I could just create a secondary query in which I spot this double-parent keys and then exclude rows based on this.
But I wonder if there's a shortest way to do it, working with the hiearchy itself.... if there's a way to know that a key already has another parent.
(Since this goes in a view, I need to do this in a query, not PL/SQL.)

Thanks in advance.

Answer

I admit I am generally sql-server so this is a thought for you but it may need some tweaking and syntax help. But what about adding a ROW_NUMBER() partitioned at Child level and adding a second condition in your connect by clause to limit the recursion. Perhaps something like this?

 WITH HER(CHILD, PARENT) AS (
     SELECT 'A' AS CHILD, null  AS PARENT FROM DUAL UNION
     SELECT 'B' AS CHILD, 'A'   AS PARENT FROM DUAL UNION
     SELECT 'C' AS CHILD, 'A'   AS PARENT FROM DUAL UNION
     SELECT 'D' AS CHILD, 'C'   AS PARENT FROM DUAL UNION
     SELECT 'E' AS CHILD, 'D'   AS PARENT FROM DUAL UNION
     SELECT 'F' AS CHILD, null  AS PARENT FROM DUAL UNION
     SELECT 'G' AS CHILD, 'F'   AS PARENT FROM DUAL UNION
     SELECT 'C' AS CHILD, 'G'   AS PARENT FROM DUAL UNION  --<<--- shared
     SELECT 'H' AS CHILD,  null AS PARENT FROM DUAL UNION
     SELECT 'B' AS CHILD,  'H'  AS PARENT FROM DUAL UNION  --<<--- shared
     SELECT 'X' AS CHILD,  null AS PARENT FROM DUAL UNION
     SELECT 'Y' AS CHILD,  'X'  AS PARENT FROM DUAL UNION
     SELECT 'Z' AS CHILD,  'Y'  AS PARENT FROM DUAL UNION
     SELECT 'C' AS CHILD,  'Z'  AS PARENT FROM DUAL       --<<--- shared  
    )

    , HERChildRowNum(CHILD, PARENT, ChildRowNum) AS (

       SELECT
          CHILD,
          PARENT,
          ROW_NUMBER() OVER (PARTITION BY CHILD ORDER BY (SELECT 0)) as ChildRowNum
       FROM
          HER
    )


     SELECT 
         LPAD(' ', 7*(LEVEL-1),' ')||CHILD||' - '||PARENT
     FROM HERChildRowNum
     START WITH PARENT IS NULL
     CONNECT BY PRIOR CHILD = PARENT AND ChildRowNum = 1
Comments