Depending on how you write the query, you may want to limit the number of levels of recursion. Limiting the number of levels permits you to return only the top levels of management, for example, but may exclude some employees if the chains of command are longer than you anticipated. Providing no restriction on the number of levels ensures no employees are excluded, but can introduce infinite recursion should the execution require any cycles; for example, if an employee directly or indirectly reports to himself. This situation could arise within a company's management hierarchy if, for example, an employee within the company also sits on the board of directors.
The following query demonstrates how to list the employees by management level. Level 0 represents employees with no managers. Level 1 represents employees who report directly to one of the level 0 managers, level 2 represents employees who report directly to a level 1 manager, and so on.
WITH RECURSIVE manager ( EmployeeID, ManagerID, GivenName, Surname, mgmt_level ) AS ( ( SELECT EmployeeID, ManagerID, -- initial subquery GivenName, Surname, 0 FROM Employees AS e WHERE ManagerID = EmployeeID ) UNION ALL ( SELECT e.EmployeeID, e.ManagerID, -- recursive subquery e.GivenName, e.Surname, m.mgmt_level + 1 FROM Employees AS e JOIN manager AS m ON e.ManagerID = m.EmployeeID AND e.ManagerID <> e.EmployeeID AND m.mgmt_level < 20 ) ) SELECT * FROM manager ORDER BY mgmt_level, Surname, GivenName;
The condition within the recursive query that restricts the management level to less than 20 is an important precaution. It prevents infinite recursion if the table data contains a cycle.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|