Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Hierarchial Queries

Hierarchial Queries

The queries shown below demonstrates how to query an hierarchial database.
The queries works on employees tables.
The script for creating the table and inserting data is available here…

<br /> Hierarchial Queries<br />


Query to list hierachially starting from TOP

The following query lists all employees hierarchially starting with "Steven King". 

SELECT  lpad(' ', level*4) ||' Emp ID :  '||EMPLOYEE_ID||'  Name :  '||FIRST_NAME||' '||LAST_NAME||'   Manager ID :  '||MANAGER_ID
 FROM    employees
 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
 START WITH MANAGER_ID IS NULL;

    Emp ID :  100  Name :  Steven King   Manager ID :
        Emp ID :  101  Name :  Neena Kochhar   Manager ID :  100
            Emp ID :  108  Name :  Nancy Greenberg   Manager ID :  101
                Emp ID :  109  Name :  Daniel Faviet   Manager ID :  108
                Emp ID :  110  Name :  John Chen   Manager ID :  108
                Emp ID :  111  Name :  Ismael Sciarra   Manager ID :  108
                Emp ID :  112  Name :  Jose Manuel Urman   Manager ID :  108
                Emp ID :  113  Name :  Luis Popp   Manager ID :  108
            Emp ID :  200  Name :  Jennifer Whalen   Manager ID :  101
            Emp ID :  203  Name :  Susan Mavris   Manager ID :  101
            Emp ID :  204  Name :  Hermann Baer   Manager ID :  101
            Emp ID :  205  Name :  Shelley Higgins   Manager ID :  101
                Emp ID :  206  Name :  William Gietz   Manager ID :  205
        Emp ID :  102  Name :  Lex De Haan   Manager ID :  100
            Emp ID :  103  Name :  Alexander Hunold   Manager ID :  102
                Emp ID :  104  Name :  Bruce Ernst   Manager ID :  103
                Emp ID :  105  Name :  David Austin   Manager ID :  103
                Emp ID :  106  Name :  Valli Pataballa   Manager ID :  103
                Emp ID :  107  Name :  Diana Lorentz   Manager ID :  103
        Emp ID :  114  Name :  Den Raphaely   Manager ID :  100
            Emp ID :  115  Name :  Alexander Khoo   Manager ID :  114
            Emp ID :  116  Name :  Shelli Baida   Manager ID :  114
            Emp ID :  117  Name :  Sigal Tobias   Manager ID :  114
            Emp ID :  118  Name :  Guy Himuro   Manager ID :  114
            Emp ID :  119  Name :  Karen Colmenares   Manager ID :  114
        Emp ID :  201  Name :  Michael Hartstein   Manager ID :  100
            Emp ID :  202  Name :  Pat Fay   Manager ID :  201

27 rows selected.

Query listing from BOTTOM to TOP

The following query lists all managers for "Luis Popp" hierarchially. Note the use of Key word PRIOR before MANAGER_ID.

SELECT  rpad(' ', level*4) ||'    '||EMPLOYEE_ID||'  --  '||FIRST_NAME||' '||LAST_NAME||'   --  '||MANAGER_ID
 FROM    employees
 CONNECT BY EMPLOYEE_ID = PRIOR  MANAGER_ID
 START WITH EMPLOYEE_ID=113;

       113  --  Luis Popp   --  108
           108  --  Nancy Greenberg   --  101
               101  --  Neena Kochhar   --  100
                   100  --  Steven King   --

Starting with a particular Manager

The following query lists all employees hierarchially starting with "Neena Kochhar". 

SELECT  lpad(' ', level*4) ||'   '||EMPLOYEE_ID||'  --  '||FIRST_NAME||' '||LAST_NAME||'   --  '||MANAGER_ID
 FROM    employees
 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
 START WITH EMPLOYEE_ID = 101;

      101  --  Neena Kochhar   --  100
          108  --  Nancy Greenberg   --  101
              109  --  Daniel Faviet   --  108
              110  --  John Chen   --  108
              111  --  Ismael Sciarra   --  108
              112  --  Jose Manuel Urman   --  108
              113  --  Luis Popp   --  108
          200  --  Jennifer Whalen   --  101
          203  --  Susan Mavris   --  101
          204  --  Hermann Baer   --  101
          205  --  Shelley Higgins   --  101
              206  --  William Gietz   --  205

12 rows selected.

Concatenating the fields in the hierarchy

The SQL function SYS_CONNECT_BY_PATH( can be used to get a concatenated string using all columns in the hierarchy

SELECT LPAD(' ', 4*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  FROM employees
  START WITH last_name = 'Kochhar'
  CONNECT BY PRIOR employee_id = manager_id;

  /Kochhar
      /Kochhar/Greenberg
          /Kochhar/Greenberg/Faviet
          /Kochhar/Greenberg/Chen
          /Kochhar/Greenberg/Sciarra
          /Kochhar/Greenberg/Urman
          /Kochhar/Greenberg/Popp
      /Kochhar/Whalen
      /Kochhar/Mavris
      /Kochhar/Baer
      /Kochhar/Higgins
          /Kochhar/Higgins/Gietz

12 rows selected.

Create Table

CREATE TABLE employees
 ( employee_id NUMBER(6)
 , first_name VARCHAR2(20)
 , last_name VARCHAR2(25)
        CONSTRAINT emp_last_name_nn NOT NULL
 , email VARCHAR2(25)
       CONSTRAINT emp_email_nn NOT NULL
 , phone_number VARCHAR2(20)
 , hire_date DATE
       CONSTRAINT emp_hire_date_nn NOT NULL
 , job_id VARCHAR2(10)
       CONSTRAINT emp_job_nn NOT NULL
 , salary NUMBER(8,2)
 , commission_pct NUMBER(2,2)
 , manager_id NUMBER(6)
 , department_id NUMBER(4)
 , CONSTRAINT emp_salary_min
 CHECK (salary > 0)
 , CONSTRAINT emp_email_uk
 UNIQUE (email)
 ) ;

Insert Values

insert into employees values(100,'Steven','King','SKING','515.123.4567',
to_date('17-JUN-87','dd-mon-yy'),'AD_PRES',24000,null,null,90 );

insert into employees values(101,'Neena','Kochhar','NKOCHHAR','515.123.4568',
to_date('21-SEP-89','dd-mon-yy'),'AD_VP',17000,null,100,90 );

insert into employees values(102,'Lex','De Haan','LDEHAAN','515.123.4569',
to_date('13-JAN-93','dd-mon-yy'),'AD_VP',17000,null,100,90 );

insert into employees values(103,'Alexander','Hunold','AHUNOLD','590.423.4567',
to_date('03-JAN-90','dd-mon-yy'),'IT_PROG',9000,null,102,60 );

insert into employees values(104,'Bruce','Ernst','BERNST','590.423.4568',
to_date('21-MAY-91','dd-mon-yy'),'IT_PROG',6000,null,103,60 );

insert into employees values(105,'David','Austin','DAUSTIN','590.423.4569',
to_date('25-JUN-97','dd-mon-yy'),'IT_PROG',4800,null,103,60 );

insert into employees values(106,'Valli','Pataballa','VPATABAL','590.423.4560'
,to_date('05-FEB-98','dd-mon-yy'),'IT_PROG',4800,null,103,60 );

insert into employees values(107,'Diana','Lorentz','DLORENTZ','590.423.5567',
to_date('07-FEB-99','dd-mon-yy'),'IT_PROG',4200,null,103,60 );

insert into employees values(108,'Nancy','Greenberg','NGREENBE','515.124.4569',
to_date('17-AUG-94','dd-mon-yy'),'FI_MGR',12000,null,101,100 );

insert into employees values(109,'Daniel','Faviet','DFAVIET','515.124.4169',
to_date('16-AUG-94','dd-mon-yy'),'FI_ACCOUNT',9000,null,108,100 );

insert into employees values(110,'John','Chen','JCHEN','515.124.4269',
to_date('28-SEP-97','dd-mon-yy'),'FI_ACCOUNT',8200,null,108,100 );

insert into employees values(111,'Ismael','Sciarra','ISCIARRA','515.124.4369',
to_date('30-SEP-97','dd-mon-yy'),'FI_ACCOUNT',7700,null,108,100 );

insert into employees values(112,'Jose Manuel','Urman','JMURMAN','515.124.4469',
to_date('07-MAR-98','dd-mon-yy'),'FI_ACCOUNT',7800,null,108,100 );

insert into employees values(113,'Luis','Popp','LPOPP','515.124.4567',
to_date('07-DEC-99','dd-mon-yy'),'FI_ACCOUNT',6900,null,108,100 );

insert into employees values(114,'Den','Raphaely','DRAPHEAL','515.127.4561',
to_date('07-DEC-94','dd-mon-yy'),'PU_MAN',11000,null,100,30 );

insert into employees values(115,'Alexander','Khoo','AKHOO','515.127.4562',
to_date('18-MAY-95','dd-mon-yy'),'PU_CLERK',3100,null,114,30 );

insert into employees values(116,'Shelli','Baida','SBAIDA','515.127.4563',
to_date('24-DEC-97','dd-mon-yy'),'PU_CLERK',2900,null,114,30 );

insert into employees values(117,'Sigal','Tobias','STOBIAS','515.127.4564',
to_date('24-JUL-97','dd-mon-yy'),'PU_CLERK',2800,null,114,30 );

insert into employees values(118,'Guy','Himuro','GHIMURO','515.127.4565',
to_date('15-NOV-98','dd-mon-yy'),'PU_CLERK',2600,null,114,30 );

insert into employees values(119,'Karen','Colmenares','KCOLMENA','515.127.4566',
to_date('10-AUG-99','dd-mon-yy'),'PU_CLERK',2500,null,114,30 );

insert into employees values(200,'Jennifer','Whalen','JWHALEN','515.123.4444',
to_date('17-SEP-87','dd-mon-yy'),'AD_ASST',4400,null,101,10 );

insert into employees values(201,'Michael','Hartstein','MHARTSTE','515.123.5555',
to_date('17-FEB-96','dd-mon-yy'),'MK_MAN',13000,null,100,20 );

insert into employees values(202,'Pat','Fay','PFAY','603.123.6666',
to_date('17-AUG-97','dd-mon-yy'),'MK_REP',6000,null,201,20 );

insert into employees values(203,'Susan','Mavris','SMAVRIS','515.123.7777',
to_date('07-JUN-94','dd-mon-yy'),'HR_REP',6500,null,101,40 );

insert into employees values(204,'Hermann','Baer','HBAER','515.123.8888',
to_date('07-JUN-94','dd-mon-yy'),'PR_REP',10000,null,101,70 );

insert into employees values(205,'Shelley','Higgins','SHIGGINS','515.123.8080',
to_date('07-JUN-94','dd-mon-yy'),'AC_MGR',12000,null,101,110 );

insert into employees values(206,'William','Gietz','WGIETZ','515.123.8181',
to_date('07-JUN-94','dd-mon-yy'),'AC_ACCOUNT',8300,null,205,110 );
This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

Post a Comment

You must be logged in to post a comment.