Systems Engineering and RDBMS

Traversing the hierarchy – Part II

Posted by decipherinfosys on April 26, 2008

Yesterday, we had covered how to traverse a hierarchy using CTEs. We had used the schema sample from a pharmaceutical industry as an example. In today’s post, let’s make use of a more generic example. This one pertains to a recursive 1:N relationship within the same table – and this scenario exists in almost all of the HR applications out there. An employee has a manager and the top dog in the company does not have anyone to report to (well, they do have to report to the board and the shareholders of the company but they do not have those set up as employees in the day to day activities of the organization :-)).

So, let’s take a look at the table and the data that we will use to illustrate this: The hierarchy relationship example can also be applied to the employees of a company. Let’s say that the table name is EMP_MASTER with this structure:

CREATE TABLE EMP_MASTER
(
EMP_NBR INT NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR(20) NOT NULL,
MGR_NBR INT NULL
)
GO

/******************************************
Now, let us insert some data into this
This uses the SQL Server syntax
*******************************************/

SET NOCOUNT ON

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 1);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 2);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 2);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 6);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 7);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 8);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 6);
INSERT INTO EMP_MASTER VALUES (11, ‘JOHN’, 5);
INSERT INTO EMP_MASTER VALUES (12, ‘LUTHER’, 11);
INSERT INTO EMP_MASTER VALUES (13, ‘GARY’, 11);
INSERT INTO EMP_MASTER VALUES (14, ‘ROBERTO’, 12);
INSERT INTO EMP_MASTER VALUES (15, ‘IRA’, 13);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

/******************************************
Let’s do a simple select to look
at the data
*******************************************/

SELECT * FROM dbo.EMP_MASTER

EMP_NBR     EMP_NAME             MGR_NBR

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

1           DON                  5

2           HARI                 1

3           RAMESH               2

4           JOE                  2

5           DENNIS               NULL

6           NIMISH               5

7           JESSIE               6

8           KEN                  7

9           AMBER                8

10          JIM                  6

11          JOHN                 5

12          LUTHER               11

13          GARY                 11

14          ROBERTO              12

15          IRA                  13

/******************************************
Let’s create a view which will use a
CTE in order to traverse through
the hierarchy
*******************************************/

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ’emp_hierarchy’
AND TABLE_SCHEMA = ‘dbo’
)
BEGIN
PRINT ‘View emp_hierarchy already exists…Dropping it and recreating’
DROP VIEW emp_hierarchy
END
ELSE
BEGIN
PRINT ‘Creating View emp_hierarchy’
END
GO
create view dbo.emp_hierarchy
as
with emp (traverse_path, emp_id, mgr_id, emp_name, level, lineage)
as (
select CONVERT(VARBINARY(MAX), emp_name) AS traverse_path,
emp_nbr,
Null as mgr_id,
emp_name,
1,
cast((‘/’ + str(emp_nbr,6,0) + ‘/’) as varchar(255))
from dbo.emp_master
where mgr_nbr is null
/*– the highest level in the organization*/

union all

select e.traverse_path + CONVERT(VARBINARY(MAX), em.emp_name) as traverse_path,
emp_nbr,
mgr_nbr,
em.emp_name,
e.level + 1,
cast((e.lineage + str(em.emp_nbr,6,0) + ‘/’) as varchar(255))
from dbo.emp_master as em
inner join emp e
on e.emp_id = em.mgr_nbr
)
select * from emp;
GO

If you look at the above view, it is only slightly different than the example that we had shown yesterday. The reason for the difference is that in yesterday’s example, we were handling a M:N recusrive relationship between the same entity and thus there were two tables to deal with while in today’s example, we are dealing with a single table and a 1:N recursive relationship. The logic remains exactly the same as before. We get the top dog in the company as the first record and then we do a UNION ALL operation to the recursive tree and use the traverse path as an ordering mechanism so that we can see the actual tree. The trick is to cast the employee name as a VARBINARY(MAX) and keep on appending it so that we can later on order by on it to see the complete tree structure for the organization. That helps us to see from the CEO – down to the SVP and their individual department immediately down from there till the deepest level is reached. And that is then followed by the next SVP and their hierarchy in the same fashion. And this continues that way to show a complete tree representation. The lineage column is used to just show the lineage of the tree.

You can see it by executing the following command:

select * from emp_hierarchy order by traverse_path
GO

Since the output is rather large, we will leave it up to you to execute these in your environment to see it. This is a quick and fast way to recursively build up hierarchy trees in SQL Server 2005 or Oracle and DB2 (with slight syntax differences). SQL Server 2008 also introduces a new data-type for hierarchy which we will cover in a later post.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: