Reference to parent columns in Oracle CONNECT BY hierarchical query

Question

I have data that looks like this:

KEY1   KEY2      KEY3   LKEY1  LKEY2     LKEY3  FLAG
====== ========= ====== ====== ========= ====== =====
09/10  10000     A1234  09/10  AU000123  A1234  1
09/10  10000     A1234  09/10  AU000456  A1234  1
09/10  10000     A1234  09/10  AX000001  A1234  1
09/10  AX000001  A1234  09/10  AE000010  A1234  0
09/10  AX000001  A1234  09/10  AE000020  A1234  0
09/10  AX000001  A1234  09/10  AE000030  A1234  0
09/10  10000     A1234  09/10  AX000002  A1234  0
09/10  AX000002  A1234  09/10  AE000040  A1234  0
09/10  10000     A1234  09/10  AU000789  A1234  0

This is hierarchical data, whereby I would be querying against the root composite key (in this case 09/10 10000 A1234); the FLAG field refers to the 'object' identified by the LKEYx keys. There can be any number of levels of nesting. (Note that the KEY1 and KEY3 fields needn't be invariant, as in the example above, as long as the hierarchy is preserved.)

What I want to retrieve are the leaf nodes, but if a leaf's parent KEY2 is the same length as LKEY2 or contains an X as the second character, then return the immediate parent. In this case, we also need to mark the record as optional... So, something like this:

KEY1   KEY2      KEY3   OPTION  FLAG
====== ========= ====== ======= =====
09/10  AU000123  A1234  0       1
09/10  AU000456  A1234  0       1
09/10  AX000001  A1234  1       1
09/10  AX000002  A1234  1       0
09/10  AU000789  A1234  0       0

I have written a query that does this, but it's not pretty. Moreover, it makes the assumption that all leaf nodes are at the same level down the tree in order to distinguish the optional records; however, this is not necessarily true. My query is as follows:

with queryKeys as (
  select '09/10' key1,
         '10000' key2,
         'A1234' key3,
  from   dual
),
subTree as (
  select     tree.key1,
             tree.key2,
             tree.key3,

             tree.lkey1,
             tree.lkey2,
             tree.lkey3,

             tree.flag,

             connect_by_isleaf isLeaf,
             level thisLevel

  from       tree,
             queryKeys

  start with tree.key1 = queryKeys.key1
  and        tree.key2 = queryKeys.key2
  and        tree.key3 = queryKeys.key3

  connect by tree.key1 = prior tree.lkey1
  and        tree.key2 = prior tree.lkey2
  and        tree.key3 = prior tree.lkey3
),
maxTree as (
  select max(thisLevel) maxLevel
  from   subTree
)
select lkey1 key1,
       lkey2 key2,
       lkey3 key3,
       1 - isLeaf option,
       flag

from   subTree,
       maxTree
where (isLeaf = 1 or thisLevel = maxLevel - 1)
and   (length(key2) != length(lkey2) or substr(lkey2, 2, 1) != 'X');

The reason for queryKeys is because it's used elsewhere in a larger query and can contain more than one record. The maxTree part is the problem, beyond its general quirkiness!

Now, the reason for the title of this post is because this query could be made a lot more straightforward if I could refer to the parent's FLAG field. I tried a JOIN approach to this idea -- joining the tree with itself on the relevant keys -- but, unless I'm mistaken, that would result in a recursive problem where you'd keep having to iterate up the tree to find the correct parent keys (as both the KEYx and LKEYx fields define the complete composite key for the record).

(P.S. Using Oracle 10gR2, if it makes a difference.)

Answer 1

Just use:

PRIOR FLAG 

it will give you exactly what you want - the flag field of the parent row.

subTree as (            
select     tree.key1,            
         tree.key2,            
         tree.key3,            
         tree.lkey1,            
         tree.lkey2,            
         tree.lkey3,            
         tree.flag,            
         PRIOR TREE.FLAG PRIOR_FLAG
         connect_by_isleaf isLeaf,            
         level thisLevel            

from       tree,            
         queryKeys 
(...)

Answer 2

I've assumed your post boils down to the question, "How do I reference the FLAG attribute of the parent row in a hierarchical query?"

I don't know the SQL I've come up with is correct. I apologize if it isn't. In general, though, here was my approach:

At each level in the hierarchy, I stringed together all the keys (SYS_CONNECT_BY_PATH). Then using SUBSTR, INSTR and LEVEL, I SUBSTRinged out what amounts to the parent level key. Finally, in the definition of PARENT_FLAG, I select the FLAG of a row whose key matches this SUBSTRinged-out key.

Setup:

CREATE TABLE tree (
    key1        VARCHAR2(5)
,   key2        VARCHAR2(10)
,   key3        VARCHAR2(5)
,   lkey1       VARCHAR2(5)
,   lkey2       VARCHAR2(10)
,   lkey3       VARCHAR2(5)
,   flag        VARCHAR2(1)
);
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000123','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000456','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000001','A1234','1');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000010','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000020','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000030','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000002','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000002','A1234','09/10','AE000040','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000789','A1234','0');

Query:

COL flag          FOR A4
COL same_length   FOR A11
COL has_x_2nd     FOR A9
COL full_key_path FOR A50
COL parent_key    FOR A30
COL parent_flag   FOR A11
WITH querykeys AS (
  SELECT '09/10' key1
  ,      '10000' key2
  ,      'A1234' key3
  FROM   DUAL
)
, subtree1 AS (
  SELECT     tree.key1
  ,          tree.key2
  ,          tree.key3
  ,          tree.lkey1
  ,          tree.lkey2
  ,          tree.lkey3
  ,          tree.flag
  ,          CONNECT_BY_ISLEAF isleaf
  ,          LEVEL thislevel
  ,          DECODE(LENGTH(tree.key2)
             ,      LENGTH(tree.lkey2), '1'
             ,      '0') same_length
  ,          DECODE(UPPER(SUBSTR(tree.key2,2,1))
             ,      'X', '1'
             ,      '0') has_x_2nd
  ,          SYS_CONNECT_BY_PATH(tree.key1 || '|' || tree.key2 || '|' || tree.key3,'\')
             || '\'
             || tree.lkey1 || '|' || tree.lkey2 || '|' || tree.lkey3 || '\' full_key_path
  FROM       tree
  ,          querykeys
  START WITH tree.key1 = querykeys.key1
  AND        tree.key2 = querykeys.key2
  AND        tree.key3 = querykeys.key3
  CONNECT BY tree.key1 = PRIOR tree.lkey1
  AND        tree.key2 = PRIOR tree.lkey2
  AND        tree.key3 = PRIOR tree.lkey3
)
, subtree2 AS (
    SELECT  st1.key1
    ,       st1.key2
    ,       st1.key3
    ,       st1.lkey1
    ,       st1.lkey2
    ,       st1.lkey3
    ,       st1.flag
    ,       st1.isleaf
    ,       st1.thislevel
    ,       st1.same_length
    ,       st1.has_x_2nd
    ,       st1.full_key_path
    ,       SUBSTR(st1.full_key_path
            ,      INSTR(st1.full_key_path,'\',1,st1.thislevel) + 1
            ,      INSTR(st1.full_key_path,'\',1,st1.thislevel + 1)
                   - INSTR(st1.full_key_path,'\',1,st1.thislevel) - 1) parent_key
    FROM    subtree1    st1
)
SELECT  st2.key1
,       st2.key2
,       st2.key3
,       st2.lkey1
,       st2.lkey2
,       st2.lkey3
,       st2.flag
,       st2.isleaf
,       st2.thislevel
,       st2.same_length
,       st2.has_x_2nd
,      (SELECT t_prime.flag
        FROM   tree   t_prime
        WHERE  t_prime.key1 = SUBSTR(st2.parent_key
                              ,      1
                              ,      INSTR(st2.parent_key,'|',1,1) - 1)
        AND    t_prime.key2 = SUBSTR(st2.parent_key
                              ,      INSTR(st2.parent_key,'|',1,1) + 1
                              ,      INSTR(st2.parent_key,'|',1,2)
                                   - INSTR(st2.parent_key,'|',1,1) - 1)
        AND    t_prime.key3 = SUBSTR(st2.parent_key
                              ,      INSTR(st2.parent_key,'|',1,2) + 1)
        -- Following assumes all rows with parent keys have same flag value.
        -- Avoids ORA-01427: single-row subquery returns more than one row.
        AND    ROWNUM = 1) parent_flag
FROM    subtree2    st2
;

Result:

KEY1  KEY2       KEY3  LKEY1 LKEY2      LKEY3 FLAG     ISLEAF  THISLEVEL SAME_LENGTH HAS_X_2ND PARENT_FLAG
----- ---------- ----- ----- ---------- ----- ---- ---------- ---------- ----------- --------- -----------
09/10 10000      A1234 09/10 AU000123   A1234 1             1          1 0           0         1
09/10 10000      A1234 09/10 AU000456   A1234 1             1          1 0           0         1
09/10 10000      A1234 09/10 AU000789   A1234 0             1          1 0           0         1
09/10 10000      A1234 09/10 AX000001   A1234 1             0          1 0           0         1
09/10 AX000001   A1234 09/10 AE000010   A1234 0             1          2 1           1         0
09/10 AX000001   A1234 09/10 AE000020   A1234 0             1          2 1           1         0
09/10 AX000001   A1234 09/10 AE000030   A1234 0             1          2 1           1         0
09/10 10000      A1234 09/10 AX000002   A1234 0             0          1 0           0         1
09/10 AX000002   A1234 09/10 AE000040   A1234 0             1          2 1           1         0

9 rows selected.

SQL>

As I said, I'm not 100% sure I grokked your data model completely, but I hope you're able to follow my approach.