Designating the node level v17
LEVEL
is a pseudo-column that you can use wherever a column can appear in the SELECT
command. For each row in the result set, LEVEL
returns a non-zero integer value designating the depth in the hierarchy of the node represented by this row. The LEVEL
for root nodes is 1. The LEVEL
for direct children of root nodes is 2, and so on.
Example
The following query is a modification of the previous query with the addition of the LEVEL
pseudo-column. In addition, using the LEVEL
value, the employee names are indented to further emphasize the depth in the hierarchy of each row.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;
The output from this query is:
level | employee | empno | mgr ------+-------------+-------+------ 1 | KING | 7839 | 2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | WARD | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 rows)
Nodes that share a common parent and are at the same level are called siblings. For example, in the above output, employees ALLEN
, WARD
, MARTIN
, TURNER
, and JAMES
are siblings since they are all at level three with parent BLAKE
. JONES
, BLAKE
, and CLARK
are siblings since they are at level two and KING
is their common parent.
- On this page
- Example