Continued from Part 1...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.