I'm seeking assistance on a non-recursive query to retrieve a list of employees spanning three hierarchical levels downward from the CEO. The current recursive query I have employs a Common Table Expression (CTE) and retrieves relevant details from the 'users' table. However, due to specific constraints, recursion cannot be used in Saviynt. The query is structured as follows:
WITH RECURSIVE EmployeeHierarchy AS ( SELECT email, username, manager, 1 AS level FROM users WHERE statuskey = 1 AND EMPLOYEETYPE = 'employee' AND MANAGER IS NULL AND JOBCODE = 'Chief Executive Officer' UNION ALL SELECT e.email, e.username, e.manager, eh.level + 1 FROM users e JOIN EmployeeHierarchy eh ON e.manager = eh.username WHERE eh.level < 3 ) SELECT email, username, manager, level FROM EmployeeHierarchy;
I'm exploring alternative approaches for querying an employee hierarchy up to three levels deep. Currently, I'm using multiple LEFT JOINs on the 'employees' table to retrieve details such as employee ID, name, and hierarchy level. The query is structured as follows:
SELECT E.employee_id, COALESCE(E.employee_name, M1.employee_name, M2.employee_name, M3.employee_name) AS employee_name, CASE WHEN E.manager_id IS NULL THEN 0 WHEN M1.employee_id IS NOT NULL THEN 1 WHEN M2.employee_id IS NOT NULL THEN 2 WHEN M3.employee_id IS NOT NULL THEN 3 END AS level FROM employees AS E LEFT JOIN
I'm open to suggestions for a more efficient way to perform this operation. While the current requirement is to retrieve details from the CEO down three levels, future scenarios may involve querying details from a director down to the bottom of the hierarchy or something in the middle.
Writing joins to identify a certain hierarchy is not Ideal
Any insights or recommendations would be greatly appreciated!!
