Q
Problem solve Get help with specific problems with your technologies, process and projects.

Showing inheritance, Part 3

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                   2
This, 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;


Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close