Connect by prior tree must be symmetrical

Question

I have a connect by prior query that builds up my tree structure in Oracle. This works fine, but I have a component that requires a symmetrical tree to display correctly.

So my idea is to inject more nodes into the tree if the node sits on a level lower than the highest level.

e.g. if we have a tree

Root
  +- Node 1
    +- Leaf 1 (Level 3)
  +- Node 2
    +- Node 3
      +- Leaf 2 (Level 4)

I need to modify the tree at runtime to look like this:

Root
  +- Node 1
    +- Copy of Node 1
      +- Leaf 1 (Level 4)
  +- Node 2
    +- Node 3
      +- Leaf 2 (Level 4)

This will make my tree symmetrical at runtime for the component to work.

Is there an easy Oracle query or function that can assist in this, or some SQL statement that can assist in this?

Answer 1

Ok, after a lot of trail and error I think I found the solution.

So if you have a test table call it xx_tree_test with 3 fields: cd, sup_cd and name; and I add the test data to it, this query

      SELECT CD,
           SUP_CD,
           LEVEL AS LVL,
           CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 'L' ELSE NULL END AS LEAF,
           LPAD (' ', 3 * LEVEL, ' .') || NAME AS NAME
      FROM xx_tree_test
      START WITH SUP_CD IS NULL
      CONNECT BY PRIOR CD = SUP_CD;

will yield this result:

To add the extra node to bring Leaf 1 and Leaf 2 to the same level you need this query:

 SELECT CD,
           SUP_CD,
           LEVEL AS LVL,
           CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 'L' ELSE NULL END AS LEAF,
           LPAD (' ', 3 * LEVEL, ' .') || NAME AS NAME         
      FROM (WITH FULL_TREE
                 AS (    SELECT CD,
                                SUP_CD,
                                LEVEL AS LVL,
                                CASE
                                   WHEN CONNECT_BY_ISLEAF = 1 THEN 'L'
                                   ELSE NULL
                                END
                                   AS LEAF,
                                LPAD (' ', 3 * LEVEL, '  .') || NAME AS TREE_NAME,
                                NAME
                           FROM XX_TREE_TEST
                     START WITH SUP_CD IS NULL
                     CONNECT BY PRIOR CD = SUP_CD)
            SELECT A.NAME,
                   A.CD,
                   A.SUP_CD,
                   A.LVL
              FROM FULL_TREE A
             WHERE NVL (LEAF, 'z') != 'L'
            UNION ALL
            SELECT CASE
                      WHEN TREE1.LVL + TREE2.ROW_NUM_GENERATED - 1 =
                              (SELECT MAX (LVL) FROM FULL_TREE)
                      THEN
                         TREE1.NAME
                      ELSE
                         'Copy of ' || TREE1.NAME
                   END
                      AS NAME,
                   CASE
                      WHEN TREE1.LVL + TREE2.ROW_NUM_GENERATED - 1 =
                              (SELECT MAX (LVL) FROM FULL_TREE)
                      THEN
                         CD
                      ELSE
                         CD || '`' || TO_CHAR (TREE2.ROW_NUM_GENERATED)
                   END
                      AS CD,
                   CASE
                      WHEN TREE2.ROW_NUM_GENERATED = 1 THEN SUP_CD
                      ELSE CD || '`' || TO_CHAR (TREE2.ROW_NUM_GENERATED - 1)
                   END
                      AS SUP_CD,
                   TREE1.LVL + TREE2.ROW_NUM_GENERATED AS LVL
              FROM    (SELECT FULL_TREE.NAME,
                              FULL_TREE.CD,
                              FULL_TREE.SUP_CD,
                              FULL_TREE.LVL
                         FROM FULL_TREE
                        WHERE LEAF = 'L') TREE1
                   JOIN
                      (    SELECT LEVEL AS ROW_NUM_GENERATED
                             FROM DUAL
                       CONNECT BY LEVEL <= (SELECT MAX (LVL) FROM FULL_TREE)) TREE2
                   ON (SELECT MAX (LVL) FROM FULL_TREE) + 1 >=
                         TREE2.ROW_NUM_GENERATED + TREE1.LVL
            ORDER BY CD, LVL)
START WITH SUP_CD IS NULL
CONNECT BY PRIOR CD = SUP_CD;

No this query will yield this result:

So all left to do now is just to package it into a nice view to hide the massive amounts of SQL.