Pseudo Columns Allowed to Use in Hierarchy Query

LEVEL

LEVEL is a pseudocolumn representing depth of hierarchical queries. The LEVEL of root node is 1 and the LEVEL of its child node is 2.

The LEVEL (pseudocolumn) can be used in the WHERE clause, ORDER BY clause, and GROUP BY ... HAVING clause of the SELECT statement. And it can also be used in the statement using aggregate functions.

The following example shows how to retrieve the LEVEL value to check level of node.

-- Checking the LEVEL value

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

The following example shows how to add LEVEL conditions after the CONNECT BY statement.

SELECT LEVEL FROM db_root CONNECT BY LEVEL <= 10;

        level

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

            1

            2

            3

            4

            5

            6

            7

            8

            9

           10

Note that the format of "CONNECT BY expr(LEVEL) < expr", for example "CONNECT BY LEVEL +1 < 5") is not supported.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF is a pseudocolumn representing that the result of hierarchy query is leaf node. If the current row is a leaf node, it returns 1; otherwise, it returns 0.

The following example shows how to retrieve the CONNECT_BY_ISLEAF value to check whether it is a leaf node or not.

-- Checking a CONNECT_BY_ISLEAF value

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

CONNECT_BY_ISCYCLE is a pseudocolumn representing 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 can be used in the WHERE, ORDER BY, and GROUP BY ... HAVING clauses of the SELECT statement. It can also used in aggregate functions.

Note This pseudocolumn is available only when the NOCYCLE keyword is used in the statement.

The following example shows how to retrieve the CONNECT_BY_ISCYCE value to check a row that occurs loop.

-- Creating a tree_cycle table and inserting data

CREATE TABLE tree_cycle(ID INT, MgrID INT, Name VARCHAR(32));

INSERT INTO tree_cycle VALUES (1,NULL,'Kim');

INSERT INTO tree_cycle VALUES (2,11,'Moy');

INSERT INTO tree_cycle VALUES (3,1,'Jonas');

INSERT INTO tree_cycle VALUES (4,1,'Smith');

INSERT INTO tree_cycle VALUES (5,3,'Verma');

INSERT INTO tree_cycle VALUES (6,3,'Foster');

INSERT INTO tree_cycle VALUES (7,4,'Brown');

INSERT INTO tree_cycle VALUES (8,4,'Lin');

INSERT INTO tree_cycle VALUES (9,2,'Edwin');

INSERT INTO tree_cycle VALUES (10,9,'Audrey');

INSERT INTO tree_cycle VALUES (11,10,'Stone');

-- Checking a CONNECT_BY_ISCYCLE value

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