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