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:
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
Photo by extranoise
