Database Reference
In-Depth Information
Figure 13.5
A Hierarchical
Query on
Hierarchical Data.
, NAME "Instrument", LEVEL
FROM INSTRUMENT
START WITH INSTRUMENT_ID = 10
CONNECT BY PRIOR INSTRUMENT_ID = SECTION_ID
ORDER BY 1, 2;
The third and fourth examples, shown next, demonstrate the use of the
new CONNECT_BY_ISCYCLE and CONNECT_BY_
ISLEAF pseudocolumns. CONNECT_BY_ISCYCLE will return 1 if a row
has a child where that child row is also an ancestor of the row. Thus in the
third example shown following the result is 0 and no further rows are
shown, because none are both children and ancestors at the same time. The
result is shown in Figure 13.7.
SELECT CONNECT_BY_ISCYCLE “IsCycle”
, INSTRUMENT_ID, NAME, SECTION_ID, LEVEL
FROM INSTRUMENT
START WITH INSTRUMENT_ID = 10
CONNECT BY NOCYCLE INSTRUMENT_ID = SECTION_ID
ORDER BY 4, 2;
