Q

Showing inheritance, Part 2a

Continued from Part 1...

2. Bottom-Up Hierarchical Query. Most of the hierarchical queries (START WITH ... CONNECT BY ... PRIOR) I've seen are top-down queries, that is, they start with parents and then show children. One of the properties of top-down queries is that no row in the table will appear twice (unless the START WITH set includes ancestors and their descendants). This is a corollary of the condition that no row has two parents. That's not what you want here: you want to show A once in its own right, a second time as the parent of B, again as the ancestor of C, yet again as the ancestor of D. To do this you need a bottom-up query, where you start with children and then show their ancestors. What determines if a hierarchical query is top-down or bottom-up? The placement of the PRIOR operator. Where top-down queries say something like "CONNECT BY parent_id = PRIOR id", bottom-up queries say "CONNECT BY id = PRIOR parent_id".

SELECT  LEVEL,
        type
FROM    parent_child
CONNECT BY
        type    = PRIOR parent;
 
     LEVEL TYPE
---------- ----------
         1 A
         1 B
         2 A
         1 C
         2 B
         3 A
         1 D
         2 B
         3 A
This shows each type and its ancestors. To get the properties, simply JOIN with the property view.
SELECT  LEVEL,
        type,
        p.property
FROM    parent_child     pc   JOIN
        property         p    USING (type)
CONNECT BY
        type    = PRIOR pc.parent;

     LEVEL TYPE         PROPERTY
---------- ---------- ----------
         1 A                   1
         1 A                   2
         1 B                   3
         2 A                   1
         2 A                   2
         1 B                   4
         2 A                   1
         2 A                   2
         1 C                   5
         2 B                   3
         3 A                   1
         3 A                   2
         2 B                   4
         3 A                   1
         3 A                   2
         1 D                   6
         2 B                   3
         3 A                   1
         3 A                   2
         2 B                   4
         3 A                   1
         3 A                   2
This is nothing other than the desired result set. You don't recognize it? That's because it needs a few changes.
  1. There are 22 rows when there should be 16. Apparently, SQL is doing the JOIN before the CONNECT BY.
  2. The order should be the same as the order in the previous result set
  3. The first column should show the starting type
  4. The second column should be NULL when it's the starting type

Part 2 is continued...


This was first published in August 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close