CONNECT_BY_ISCYCLE

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.

CONNECT_BY_ISLEAF

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
...

LEVEL

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 the illustration sqlrf001.gif

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