Hierarchical query pseudocolumns are valid only in hierarchical queries. To define a hierarchical relationship in a query, you must use the CONNECT BY clause. This topic describes how to use three types of hierarchical query pseudocolumns: CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, and LEVEL.
ONNECT_BY_ISCYCLE pseudocolumn
The CONNECT_BY_ISCYCLE pseudocolumn indicates the row from which cycling starts.
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise, it returns 0.
To specify CONNECT_BY_ISCYCLE, you must specify the NOCYCLE parameter of the CONNECT BY clause first. Otherwise, the system returns an error because of a loop in the tree-like result.
CONNECT_BY_ISLEAF pseudocolumn
The CONNECT_BY_ISLEAF pseudocolumn indicates whether the current row is a leaf of the hierarchy tree.
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf node of the tree defined by the CONNECT BY condition. Otherwise, it returns 0.
LEVEL pseudocolumn
The LEVEL pseudocolumn indicates the hierarchy level of the current row.
In the hierarchy, the root is Level 1, the children of the root are Level 2, and so forth. For example, the LEVEL pseudocolumn returns 1 for a root, 2 for a child of a root, and so forth.
Take the four-level inverted tree structure as an example. The root row is the highest row in the inverted tree, and the LEVEL pseudocolumn returns 1 for a root row. A child row is any row other than a root row. The LEVEL pseudocolumn generally returns 2, 3, or 4 for a child row. A parent row is any row, other than the root row, that has a child row. The LEVEL pseudocolumn generally returns 2 or 3 for a parent row. A leaf row is a row that does not have a child row. The LEVEL pseudocolumn generally returns 4 for a leaf row.
Hierarchical query examples
CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT);
INSERT INTO tbl1 VALUES(1, 0, -1);
INSERT INTO tbl1 VALUES(2, 1, -2);
INSERT INTO tbl1 VALUES(4, 2, -4);
INSERT INTO tbl1 VALUES(5, 2, -5);
INSERT INTO tbl1 VALUES(3, 1, -3);
INSERT INTO tbl1 VALUES(6, 3, -6);
INSERT INTO tbl1 VALUES(7, 3, -7);
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT col1,CONNECT_BY_ROOT col2 FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOTCOL1 | CONNECT_BY_ROOTCOL2 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 |
| 2 | 1 | 2 | 0 | 0 | 1 | 0 |
| 4 | 2 | 3 | 1 | 0 | 1 | 0 |
| 5 | 2 | 3 | 1 | 0 | 1 | 0 |
| 3 | 1 | 2 | 0 | 0 | 1 | 0 |
| 6 | 3 | 3 | 1 | 0 | 1 | 0 |
| 7 | 3 | 3 | 1 | 0 | 1 | 0 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
7 rows in set
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT (col1 + col2) FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+----------------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOT(COL1+COL2) |
+------+------+-------+-------------------+--------------------+----------------------------+
| 1 | 0 | 1 | 0 | 0 | 1 |
| 2 | 1 | 2 | 0 | 0 | 1 |
| 4 | 2 | 3 | 1 | 0 | 1 |
| 5 | 2 | 3 | 1 | 0 | 1 |
| 3 | 1 | 2 | 0 | 0 | 1 |
| 6 | 3 | 3 | 1 | 0 | 1 |
| 7 | 3 | 3 | 1 | 0 | 1 |
+------+------+-------+-------------------+--------------------+----------------------------+
7 rows in set
obclient> SELECT CONNECT_BY_ROOT col1, TO_NUMBER(CONNECT_BY_ROOT col1),
TO_CHAR(CONNECT_BY_ROOT col1), CONNECT_BY_ROOT(col1 + col2),
TO_NUMBER(CONNECT_BY_ROOT (col1 + col2)), TO_CHAR(CONNECT_BY_ROOT (col1 + col2))
FROM tbl1 START WITH col1 = 1 CONNECT BY NOCYCLE PRIOR col1 = col2;
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| CONNECT_BY_ROOTCOL1 | TO_NUMBER(CONNECT_BY_ROOTCOL1) | TO_CHAR(CONNECT_BY_ROOTCOL1) | CONNECT_BY_ROOT(COL1+COL2) | TO_NUMBER(CONNECT_BY_ROOT(COL1+COL2)) | TO_CHAR(CONNECT_BY_ROOT(COL1+COL2)) |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
7 rows in set