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.)
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
(...)