-1

I am trying to get the employee hierarchy as output with empLevel indicating the reporting layer. However, my query returns only one row i.e. The CEO of the company while it's supposed to return all employee names with the organization hierarchy.

WITH emp_hier AS
  (
    SELECT BOSS.employee_number, BOSS.name, BOSS.manager, 0 as empLevel
    FROM Employees as BOSS
    WHERE BOSS.manager = ''

    UNION ALL

    SELECT emp.employee_number, emp.name, emp.manager, e.empLevel + 1
    FROM Employees as emp
    INNER JOIN emp_hier as e ON emp.manager = e.employee_number

  )
  SELECT * FROM emp_hier
  • 5
    Please add DDL for tables referenced in the query. – zgguy Jun 15 '15 at 7:43
  • 2
    Do you definitiely have a record where BOSS.manager = ''? Could it be NULL instead? – Mark Sinkinson Jun 15 '15 at 8:04
  • 1
    If you see only one row (the "BOSS"), then there is no employee that matches the (emp.manager = boss.employee_number) condition. – ypercubeᵀᴹ Jun 15 '15 at 9:15
  • When I do the select using the employee number of the BOSS it neatly shows up all the reportees of the BOSS. – dukes Jun 15 '15 at 15:37
  • @dukes show us the exact query you use that works. And the exact result you get from this (non-working) query. – ypercubeᵀᴹ Jun 15 '15 at 16:12
0

If your manager column is an INT type, it'll probably be NULL or 0 when it has no manager assigned.

WITH emp_hier AS
 (
  SELECT BOSS.employee_number, BOSS.name, BOSS.manager, 0 as empLevel
  FROM Employees as BOSS
   WHERE BOSS.manager is null

  UNION ALL

  SELECT emp.employee_number, emp.name, emp.manager, e.empLevel + 1
  FROM Employees as emp
  INNER JOIN emp_hier as e ON emp.manager = e.employee_number

  )
  SELECT * FROM emp_hier
  • The manager is not INT , it's a nvarchar. It just gives me the output of anchor query. I am really puzzled at this!! – dukes Jun 15 '15 at 15:35
  • Is the employee_number also an nvarchar? – ypercubeᵀᴹ Jun 15 '15 at 16:16
  • Got it solved!! Some data corruption issue at employee number and it was causing all this chaos. Thanks everyone who helped me in this. – dukes Jun 15 '15 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.