LEVEL is a pseudo column that can be used wherever a column can appear in the SELECT statement. 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 value for root nodes is 1. The LEVEL value for direct child nodes of root nodes is 2. The LEVEL values for other nodes are calculated in a similar way.
The following query is modified based on the previous query. The LEVEL pseudo column is added to the following query. Based on the LEVEL value, the employee names are indented to 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 following example shows the output of this query:
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 preceding output, the employees including ALLEN, WARD, MARTIN, TURNER, and JAMES are siblings, because they are all at level 3 for parent BLAKE. JONES, BLAKE, and CLARK are siblings, because they are at level 2 and KING is their common parent.