Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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



Pseudo Columns Available in Hierarchical Queries

LEVEL

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

The LEVEL (pseudo column) 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 view level of node.

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

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 example shows how to execute 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