Node Level v12

LEVEL is a pseudo-column that can be used 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.

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

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.