Connect by prior tree must be symmetrical

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 w开发者_Go百科ork.

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

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.

I do not think it can be done in SQL, or at least I cannot think of a way of doing it. It seems to me that the query would have to know how many levels to expect before it executes.

Perhaps, then, you need a temporary table so you can perform a second pass in your logic to get it the way you want it.

Do you have a client-side component that will display these data? If so then that might be the simplest place to do this second pass.