LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy.

Consider the hierarchy of employees in SCOTT’s EMP table, shown in tree structure like below:

The topmost node (root node) is Level 1, the next below is 2, and so on.

This value can be retrieved via a queries of this form:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

SQL> -- level displayed for each row SQL> select empno, ename   2       , level   3  from emp   4  start with ename = 'JONES'   5  connect by prior empno = mgr   6  /        EMPNO ENAME        LEVEL ---------- ------- ----------       7566 JONES            1       7788 SCOTT            2       7876 ADAMS            3       7902 FORD             2       7369 SMITH            3   SQL> break on level skip 1   SQL> -- level+hierarchy path displayed for each row SQL> select empno, ename   2       , level   3       , sys_connect_by_path(ename, ' -> ') path   4  from emp   5  start with ename = 'JONES'   6  connect by prior empno = mgr   7  order by level   8  /        EMPNO ENAME   LEVEL PATH ---------- ------- ----- -----------------------       7566 JONES       1  -> JONES         7902 FORD        2  -> JONES -> FORD       7788 SCOTT          -> JONES -> SCOTT         7369 SMITH       3  -> JONES -> FORD -> SMITH       7876 ADAMS          -> JONES -> SCOTT -> ADAMS