Hierarchical SQL

In a previous post, I showed an example of a self-join query. In this post, I will show examples of using Hierarchical SQL is to display self-join data in a more intuitive way. Hierarchical SQL is an extension of Oracle PL/SQL. This extension to the standard SQL provides a way to loop through all the branches and levels of multi-level self-join data. Consider the following SQL query:

SELECT last_name,||' '||'reports'||' '|| to'||' '||
nvl(PRIOR last_name, 'no one') "Reporting Relationship"
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

Here, the hierarchical query clause allows you to select rows in a hierarchical order. The nvl function returns the value of 'no one' if the last_name is null. The Prior keyword is used to link the current record to the "prior" record, or next higher in the hierarchy. The Connect By key words are used to establish a link between the primary and foreign key. The execution of this query would return the result set as seen below:

Reporting Relationship
--------------------------------------------------------------
King reports to no one
Kochhar reports to King
Greenberg reports to Kochhar
Faviet reports to Greenberg
Chen reports to Greenberg
Sciarra reports to Greenberg
Urman reports to Greenberg
Popp reports to Greenberg

Using hierarchical SQL, you could also create an organizational chart from this query statement:

SELECT lpad(last_name,Length(last_name)+(LEVEL*4)-4,'-') as "Organization Chart"
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

The lpad function returns a character string padded on the left side;the Length function returns the length of the string. The Level keyword indicates the level of the hierarchy(number of nested levels). Here we are creating an expression that creates more hyphens the deeper the nesting level. This query would produce the following result:

Organization Chart
King
----Kochhar
--------Greenberg
------------Faviet
------------Chen
------------Sciarra
------------Urman
------------Popp
--------Whalen
--------Mavris
--------Baer
--------Higgins
------------Gietz
----De Haan
--------Hunold
------------Ernst
------------Austin
------------Pataballa
------------Lorentz

As shown in these examples, hierarchical SQL is a nice tool to have in your database query toolkit.