Database Reference
In-Depth Information
13.2.1
Hierarchical Query Operators
PRIOR
. Used with the CONNECT BY condition evaluating the
subsequent expression for each parent row of each current row, using
a current row column to hook into a parent row column.
. Performs a similar function to that
of CONNECT BY PRIOR except using the root row of the hierar-
chy as opposed to the parent row.
CONNECT_BY_ROOT
13.2.2
Hierarchical Query Pseudocolumns
LEVEL
. Used only in hierarchical queries (using the CONNECT BY
clause). This returns the level (1, 2, etc.) of the row.
.
These pseudocolumns determine if hierarchical data can be expanded
upon. Does an element have ancestor and/or child entries?
Now let's demonstrate use of hierarchical queries.
CONNECT_BY_ISLEAF and CONNECT BY_ISCYCLE
13.2.3
Using Hierarchical Queries
In this first example, PRIOR is used with the CONNECT BY condition
evaluating the subsequent expression for each parent row of each current
row, using a current row column to hook into a parent row column. Figure
13.5 shows the result. The START WITH modifier simply begins at a spe-
cific point within the hierarchy.
SELECT INSTRUMENT_ID, NAME, SECTION_ID, LEVEL
FROM INSTRUMENT
START WITH INSTRUMENT_ID = 10
CONNECT BY PRIOR INSTRUMENT_ID = SECTION_ID
ORDER BY 4, 2;
Notice in Figure 13.5 that the LEVEL column is included in the query.
All the brass instruments are in the brass section. The row Brass is therefore
level 1 and the other rows, the brass instruments section, are all level 2.
The following second example shows CONNECT_BY_ROOT
performing a similar function to that of CONNECT BY PRIOR except
using the root row of the hierarchy as opposed to the parent row. The previ-
ous query is changed, as shown with the result in Figure 13.6.
SELECT CONNECT_BY_ROOT NAME "Section"
