title: Reference to parent columns in Oracle CONNECT BY hierarchical query
body: <p>I have data that looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is hierarchical data, whereby I would be querying against the root composite key (in this case <code>09/10 10000 A1234</code>); the <code>FLAG</code> field refers to the 'object' identified by the <code>LKEYx</code> keys. There can be any number of levels of nesting. (Note that the <code>KEY1</code> and <code>KEY3</code> fields needn't be invariant, as in the example above, as long as the hierarchy is preserved.)</p>
  
  <p>What I want to retrieve are the leaf nodes, <strong>but</strong> if a leaf's parent <code>KEY2</code> is the same length as <code>LKEY2</code> or contains an <code>X</code> 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>The reason for <code>queryKeys</code> is because it's used elsewhere in a larger query and can contain more than one record. The <code>maxTree</code> part is the problem, beyond its general quirkiness!</p>
  
  <p>Now, the reason for the title of this post is because this query could be made <em>a lot</em> more straightforward if I could refer to the parent's <code>FLAG</code> field. I tried a <code>JOIN</code> 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 <code>KEYx</code> and <code>LKEYx</code> fields define the complete composite key for the record).</p>
  
  <p>(P.S. Using Oracle 10gR2, if it makes a difference.)</p>