Operators Allowed to Use in Hierarchy Query

CONNECT_BY_ROOT Operator

The CONNECTION_BY_ROOT operator returns the value of a root row as a column value.

This operator can be used in the WHERE and ORDER BY clauses of the SELECT statement.

The following example shows how to retrieve the root row's id value.

-- Checking the id value of a root row for each row

SELECT id, mgrid, name, CONNECT_BY_ROOT id

    FROM tree

    START WITH mgrid IS NULL

    CONNECT BY PRIOR id=mgrid

    ORDER BY id;

id  mgrid       name        connect_by_root id

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

1   null        Kim     1

2   null        Moy     2

3   1       Jonas       1

4   1       Smith       1

5   2       Verma       2

6   2       Foster      2

7   6       Brown       2

PRIOR Operator

The PRIOR operator returns the value of a parent row as a column value and returns NULL for the root row.

This operator can be used in the WHERE, ORDER BY, and CONNECT BY clauses of the SELECT statement.

The following example shows how to retrieve the parent row's id value.

-- Checking the id value of a parent row for each row

SELECT id, mgrid, name, PRIOR id as "prior_id"

    FROM tree

    START WITH mgrid IS NULL

    CONNECT BY PRIOR id=mgrid

    ORDER BY id;

id  mgrid       name        prior_id

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

1   null        Kim     null

2   null        Moy     null

3   1       Jonas       1

4   1       Smith       1

5   2       Verma       2

6   2       Foster  2

7   6       Brown       6