Pseudo-Columns Available When Using the CONNECT BY Clause

LEVEL

This pseudo-column represents the level of the node in the hierarchy. Root nodes are considered to be at level 1, their children level 2 and so on.

The LEVEL pseudo-column may be used in the SELECT list, WHERE clause, ORDER BY clause, GROUP BY ... HAVING clauses and also in aggregate functions.

The following is an example of executing a hierarchical query with LEVEL.

-- Executing a hierarchical query with LEVEL

SELECT id, mgrid, name, LEVEL

    FROM tree

    WHERE LEVEL=2

    START WITH mgrid IS NULL

    CONNECT BY PRIOR id=mgrid

    ORDER BY id;

id    mgrid        name        level

=========================================

3    1            Jonas        2

4    1            Smith        2

5    2            Verma        2

6    2            Foster       2

CONNECT_BY_ISLEAF

This pseudo-column indicates whether a hierarchical node is a leaf node or not. If the value for a row is 1, then the associated node is a leaf node.; otherwise, it will have the value 0 indicating that the node has children.

In this example, the CONNECT_BY_ISLEAF shows that the rows with the IDs 3, 4, 5 and 7 have no children.

-- Executing a hierarchical query with CONNECT_BY_ISLEAF

SELECT id, mgrid, name, CONNECT_BY_ISLEAF

      FROM tree

      START WITH mgrid IS NULL

      CONNECT BY PRIOR id=mgrid

      ORDER BY id;

id    mgrid        name        connect_by_isleaf

===========================================================

1    null          Kim         0

2    null          Moy         0

3    1             Jonas       1

4    1             Smith       1

5    2             Verma       1

6    2             Foster      0

7    6             Brown       1

CONNECT_BY_ISCYCLE

This pseudo-column indicates that a cycle was detected while processing the node, meaning that a child was also found to be an ancestor. A value of 1 for a row means a cycle was detected; the pseudo-column's value is 0, otherwise.

The CONNECT_BY_ISCYCLE pseudo-column may be used in the SELECT list, WHERE clause, ORDER BY clause, GROUP BY and HAVING clauses and also in aggregate functions (when the GROUP BY class exists in the statement).

Note This pseudo-column is available only when the NOCYCLE keyword is used in the statement.

The following is an example of executing a hierarchical query with CONNECT_BY_ISCYCE operator.

-- --Executing a hierarchical query with CONNECT_BY_ISCYCLE

SELECT id, mgrid, name, CONNECT_BY_ISCYCLE

    FROM tree_cycle

    START WITH name in ('Kim', 'Moy')

    CONNECT BY NOCYCLE PRIOR id=mgrid

    ORDER BY id;

id  mgrid       name        connect_by_iscycle

==========================================================

1   null        Kim     0

2   11      Moy     0

3   1       Jonas       0

4   1       Smith       0

5   3       Verma       0

6   3       Foster      0

7   4       Brown       0

8   4       Lin     0

9   2       Edwin       0

10  9       Audrey      0

11  10      Stone       1