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 AThis 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 2This is nothing other than the desired result set. You don't recognize it? That's because it needs a few changes.
- There are 22 rows when there should be 16. Apparently, SQL is doing the JOIN before the CONNECT BY.
- The order should be the same as the order in the previous result set
- The first column should show the starting type
- The second column should be NULL when it's the starting type
Part 2 is continued...
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.