Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Hierarchical Query Pseudocolumns

The hierarchical query pseudocolumns are valid only in hierarchical queries. The hierarchical query pseudocolumns are:


The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

See Also:

"Hierarchical Queries " for more information about the NOCYCLE parameter and "Hierarchical Query Examples" for an example that uses the CONNECT_BY_ISCYCLE pseudocolumn


The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.


The following example shows the first three levels of the hr.employees table, indicating for each row whether it is a leaf row (indicated by 1 in the IsLeaf column) or whether it has child rows (indicated by 0 in the IsLeaf column):

SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

Employee            IsLeaf      LEVEL Path
--------------- ---------- ---------- -----------------------------------
King                     1          1 /King
Russell                  0          2 /King/Russell
Tucker                   1          3 /King/Russell/Tucker
Bernstein                1          3 /King/Russell/Bernstein
Hall                     1          3 /King/Russell/Hall
Olsen                    1          3 /King/Russell/Olsen
Cambrault                1          3 /King/Russell/Cambrault
Tuvault                  1          3 /King/Russell/Tuvault
Partners                 0          2 /King/Partners
King                     1          3 /King/Partners/King
Sully                    1          3 /King/Partners/Sully
McEwen                   1          3 /King/Partners/McEwen
Smith                    1          3 /King/Partners/Smith
Doran                    1          3 /King/Partners/Doran
Sewall                   1          3 /King/Partners/Sewall
Errazuriz                0          2 /King/Errazuriz
Vishney                  1          3 /King/Errazuriz/Vishney


For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 3-1 shows the nodes of an inverted tree with their LEVEL values.

Figure 3-1 Hierarchical Tree

Description of sqlrf001.gif follows
Description of the illustration sqlrf001.gif

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

See Also:

"Hierarchical Queries " for information on hierarchical queries in general and "IN " for restrictions on using the LEVEL pseudocolumn