Hierarchical queries :

I have explained lot of advanced sql topics like materialized view in sql , partitioning techniques in oracle. In this article i will try to explain different hierarchical queries in oracle sql with real life examples.Hierarchical queries are useful in reporting  purpose and when user wants to fetch the data from hierarchical structure these queries are very useful.Before going to the details of hierarchical queries first question comes to the mind is ‘What is mean by Hierarchy?’.

Hierarchy is nothing but the parent child relationship within same table or the view

Hierarchical queries are very traditional and one of the useful feature in day to day life.Every company has hierarchical data.The traversing of the hierarchy is bottom up or top down.

Examples :

Bottom up hierarchy :

If user wants to find the top hierarchy of employee then bottom up hierarchy is used.

Top down hierarchy :

If user finds out the employees under manager then top down hierarchy is used.

Hierarchical Queries with Examples :

There are 2 types of Hierarchical Queries in oracle:

1.Basic hierarchical query

2.Cyclic hierarchical query

In this section i will try give the real life example of the concept.To write the query the data in the table should be in hierarchical structure.

Step 1 :

Create the data in hierarchical structure

create table Employee_M
(Ename varchar2(20),employee_id varchar2(20),manager_id varchar2(20));

insert into Employee_M
values(‘Amit’,’1′,’10’);
commit;

insert into Employee_M
values(‘Rakesh’,’10’,’2′);
commit;
insert into Employee_M
values(‘Anmol’,’3′,’2′);
commit;

insert into Employee_M
values(‘Karbhari’,’2′,null);
commit;

insert into Employee_M
values(‘rohit’,’4′,’10’);
commit;

Output :

Step 2 : Connect by ,Start with and prior clause :

Question : Write the query which will gives us the records of the Employee and its manager?

To build the hierarchical query second most important part is use of connect by and prior keywords.These are two mandatory keywords to build hierarchy.Hierarchy will build like one row is parent of another row and Start with keyword defines the ancestor.Here the real life example is if user wants to see the manager name of employee then how that query will work.

To show the top to bottom hierarchy :

select ename, employee_id, manager_id, prior ename, level
from Employee_M
connect by prior employee_id = manager_id
start with manager_id is null;

Output :

In above example we need to start with the keyword prior,

1.PRIOR is a unary operator which is used or indicates that “father of” the records or first record.

2.START WITH clause is used to find out from which record user needs to start the hierarchy. In above example we have started hierarchy with the employee whose manager id is null.

3.LEVEL is a pseudocolumn which gives user the idea about the depth of the hierarchy.

4.CONNECT BY gives user the idea about the first sibling record with the specified condition.

Step 3 : Show the records in hierarchical structure 

User can show the records into hierarchical structure using following query :

select lpad(‘ ‘,level*5,’ ‘)||ename, employee_id, manager_id, prior ename, level
from Employee_M
connect by prior employee_id = manager_id
start with manager_id is null
order siblings by ename;

Output :

Step 4 : If Manager is manager of himself then what to do?

There are the situations where manager is manager of himself/herself. So We will create scenario of the same :

update Employee_M set manager_id=’2′ where employee_id=’2′;
commit;

If you run the same query then output will be blank. Lets make small change and check :

select lpad(‘ ‘,level*5,’ ‘)||ename, employee_id, manager_id, prior ename, level
from Employee_M
connect by prior employee_id = manager_id
start with manager_id=’2’
order siblings by ename;

The following error will come :

In that case oracle will not create the hierarchy for the query. So to manage that oracle has provided NOCYCLE clause.

select lpad(‘ ‘,level*5,’ ‘)||ename, employee_id, manager_id, prior ename, level
from Employee_M
connect by nocycle prior employee_id = manager_id
start with manager_id=’2’
order siblings by ename;

Output :

Step 5 : Query using connect_by_root 

This is another important unary operator in oracle which will gives the basic idea about the root of the hierarchy.If you want to see the boss’s hierarchy then user needs to use connect_by_root keyword .

select lpad(‘ ‘,level*3,’ ‘)||ename name,
connect_by_root ename boss
from Employee_M
connect by prior employee_id = manager_id
start with manager_id is null;

Output:

Here the employee named Karbhari is boss of all other employees.

Step 6 : If user wants to see the bosses of employee 

In this case user needs to use keyword ‘SYS_CONNECT_BY_PATH(column_name,Delimiter)’

Query :

select lpad(‘ ‘,level*3,’ ‘)||ename name,
SYS_CONNECT_BY_PATH(ename,’/’) boss
from Employee_M
connect by prior employee_id = manager_id
start with manager_id is null;

Output :

Some Important Additions suggested by user Paul Gibbs :

Using the Hierarchy in a PL/SQL structure, and subjecting the PL/SQL to SQL*Plus.
Are there issues with using this SELECT … START WITH … CONNECT PRIOR… with an INSERT INTO mytable in a SQL*Plus script using a PL/SQL structure (see below the “code”) when I run this in SQL*Plus, it runs and runs…. no feedback, no population in the table.

(NOTE: I took the liberty to convert my personal code and tables to the training Employee tables, some syntax or names may not be 100% accurate.)

DROP TABLE mytable;
CREAT TABLE mytable
( controlnbr number null,
lvlnbr varchar2(100) null,
emp_id varchar2(40) null,
emp_name varchar2(40) null,
mgr_id varchar2(40) null);
commit;

declare
employeeID varchar2(40) := ‘100’;
managerID varchar2(40) := ‘100’;
rec_empList myListOfEmp%rowtype; — I create a table with a list of specific employees I wish to report, just has Emp_ID
cursor myList is
select emp_ID from myListOfEmp;

begin
open myList;
<>
fetch myList into rec_empList;
if myList%NOTFOUND then
go to CloseMyList;
end if;
employeeID := rec_MyList.Emp_ID;

INSERT INTO mytable
SELECT ROWNUM,
SUBSTR(LPAD(TO_CHAR(LEVEL),LEVEL,’.’),1,25),
employeeID,
employeeName,
managerID
FROM Employee_M
start with manager_id= employeeID
connect by prior employee_id=manager_id;

goto NextRec;

<>
close myList;

end;

In above examples i explained the hierarchical queries with different examples.Hope you like this article.If you like this article or if you have  any concerns with the article kindly comment in to comments section.