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 Allowed to Use in Hierarchy Query

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

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF is a pseudo column 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 pseudo column 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 pseudo column 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