Continued from Part 2a...
To guarantee that the CONNECT BY is done before the JOIN to the property view, let's encapsulate the hierarchical part of the query in a view. By saving the ROWNUMs in from this view, we can use it to preserve the order. In fact, let's make all the desired changes in the view.
CREATE OR REPLACE VIEW ancestry AS SELECT ROWNUM AS order_num, -- for problem b DECODE ( LEVEL, 1, type, ' ' ) AS start_type, -- for problem c type, DECODE ( LEVEL, 1, NULL, type ) AS ancestor -- for problem d FROM parent_child CONNECT BY type = PRIOR parent; SELECT a.start_type AS type, a.ancestor, p.property FROM ancestry a JOIN property p USING (type) ORDER BY a.order_num, p.property; TYPE ANCESTOR PROPERTY ---------- ---------- ---------- A null 1 A null 2 B null 3 B null 4 A 1 A 2 C null 5 B 3 B 4 A 1 A 2 D null 6 B 3 B 4 A 1 A 2
Continue on to Part 3: Finding the root in hierarchical queries...
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.