Continued from Part 2...
3. Finding the Root in Hierarchical Queries. Now the only problem is the first column: where it now contains a blank, we want to carry over the last real value. In Oracle version 9, analytical functions (like LAST_VALUE) can be used for this sort of thing. What we want to do is show the type column from the row that started the CONNECT BY thread. But in any query, not just hierarchical queries, each row in the result set is drawn from one row in the table. If you want to show data from two different rows of the table in the same row of the result set, you have to do a self-join. If this were a top-down query, you could hide the self-join in a function
FUNCTION root (in_type IN glen_test.type%TYPE) RETURN glen_test.type%TYPE ...but in a bottom-up query, the root cannot be determined from anything in the row at hand. That is, there is nothing in the data of the last row that could be used to determine that the first column should be "D"; the only thing that tells us this is it's position after the row that does have "D" in the first column. Is there a way of expressing that in SQL? Yes there is: a correlated sub-query.
SELECT t.type AS type, a.ancestor, p.property FROM ancestry a JOIN property p USING (type) CROSS JOIN ancestry t WHERE t.order_num = ( SELECT MAX (order_num) FROM ancestry WHERE start_type != ' ' AND order_num <= a.order_num ) ORDER BY a.order_num, p.property; TYPE ANCESTOR PROPERTY ---------- ---------- ---------- A null 1 A null 2 B null 3 B null 4 B A 1 B A 2 C null 5 C B 3 C B 4 C A 1 C A 2 D null 6 D B 3 D B 4 D A 1 D A 2This, finally, is the desired result set.
If you find all these views inelegant, you can do the whole job in Oracle version 9 using in-line views:
WITH ancestry AS ( SELECT ROWNUM AS order_num, DECODE ( LEVEL, 1, type, ' ' ) AS start_type, type, DECODE ( LEVEL, 1, NULL, type ) AS ancestor FROM parent_child CONNECT BY type = PRIOR parent ) SELECT t.type AS type, a.ancestor, p.property FROM ancestry a JOIN ( SELECT type, property FROM glen_test ) p USING (type) CROSS JOIN ancestry t WHERE t.order_num = ( SELECT MAX (order_num) FROM ancestry WHERE start_type != ' ' AND order_num <= a.order_num ) ORDER BY a.order_num, p.property;