Thursday, 9 January 2014

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.


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


  1. It would be more representative to build a tree with different indents like: lpad(' ', (LEVEL - 1) * 2, ' | ') || ename as ename.

  2. One less obvious application of the hierarchical queries is to generate test data for development purposes:
    CREATE VIEW test_view AS
    SELECT ROWNUM AS row_id, 'test data ' || ROWNUM AS data
    FROM dual

    Note also that the "ORDER SIBLINGS BY" clause has been available for use with hierarchical queries since version 10g.


Related Posts Plugin for WordPress, Blogger...