Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Operators Available When Using the CONNECT BY Clause

CONNECT_BY_ROOT Operator

This operator can be applied to columns and it returns the parent row or root row values for that column. This operator may be used in the SELECT list, Where clause and ORDER BY clause. When using the CONNECT BY clause some column operators become available.

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

-- Executing a hierarchical query with CONNECT_BY_ROOT operator

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

This operator may be applied to a column; it will return the parent node value for that column. For a root node, the operator will return the NULL value if it is applied to a column. This operator may be used in the SELECT list, WHERE clause, ORDER BY clause and also in the CONNECT BY clause.

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

-- Executing a hierarchical query with PRIOR operator

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