Hierarchical Retrieval In ORACLE or Hierarchical Queries

There is provision to display the reporting pattern in an organization.  In the select query we can use the level (pseudocolumn), which returns 1 for a root row and 2 for a child of a root and so on.The start with clause specifies the root of the rows. Connect by specifies relationship between the parent and the child prior clause is required for a hierarchical query.

Example:

[Walking The Tree Bottom To Up]

SQL> select level,ename,job,mgr from emp start with job='PRESIDENT' connect by prior mgr=empno;

     LEVEL ENAME      JOB              MGR

     ---------- ---------- --------- ----------

         1 KING       PRESIDENT

[Walking The Tree Top To Bottom]

SQL> select ename ,' reports to ', prior ename    "walks top down" from emp  start with ename='KING' connect by prior empno=mgr;

ENAME      'REPORTSTO'  walks top

---------- ------------ ----------

KING        reports to

JONES       reports to  KING

SCOTT       reports to  JONES

ADAMS       reports to  SCOTT

FORD        reports to  JONES

SMITH       reports to  FORD

BLAKE       reports to  KING

ALLEN       reports to  BLAKE

WARD        reports to  BLAKE

MARTIN      reports to  BLAKE

TURNER      reports to  BLAKE

ENAME      'REPORTSTO'  walks top

---------- ------------ ----------

JAMES       reports to  BLAKE

CLARK       reports to  KING

MILLER      reports to  CLARK

14 rows selected.

SQL> select ename ||' reports to '||prior ename    "walks top down" from emp start with ename='KING' connect by prior empno=mgr;

walks top down

--------------------------------

KING reports to

JONES reports to KING

SCOTT reports to JONES

ADAMS reports to SCOTT

FORD reports to JONES

SMITH reports to FORD

BLAKE reports to KING

ALLEN reports to BLAKE

WARD reports to BLAKE

MARTIN reports to BLAKE

TURNER reports to BLAKE

walks top down

--------------------------------

JAMES reports to BLAKE

CLARK reports to KING

MILLER reports to CLARK

14 rows selected.

SQL>  select level ,ename ||' reports to '||prior ename    "walks top down" from emp start with ename='KING' connect by prior empno=mgr;

     LEVEL walks top down

---------- --------------------------------

         1 KING reports to

         2 JONES reports to KING

         3 SCOTT reports to JONES

         4 ADAMS reports to SCOTT

         3 FORD reports to JONES

         4 SMITH reports to FORD

         2 BLAKE reports to KING

         3 ALLEN reports to BLAKE

         3 WARD reports to BLAKE

         3 MARTIN reports to BLAKE

         3 TURNER reports to BLAKE

     LEVEL walks top down

---------- --------------------------------

         3 JAMES reports to BLAKE

         2 CLARK reports to KING

         3 MILLER reports to CLARK

14 rows selected.