below is my code:

WITH Recursive EmployeeCTE AS
( Select Employee_Id as e_id, first_Name as e_name, Manager_ID as mgr_id From Employees Where Employee_Id =&emp_id union all
Select Employees.Employee_Id as e_id, Employees.first_Name as e_name, Employees.Manager_ID as mgr_id From Employees JOIN EmployeeCTE ON Employees.Employee_Id = EmployeeCTE.Manager_ID
)
Select E1.first_name, NVL(E2.first_Name, 'No Boss') as Manager_Name
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.Manager_ID = E2.Employee_Id;

2

1 Answer

I think from an error which you are getting, try to give alias as follows:

WITH EMPLOYEECTE(E_ID, E_NAME, MGR_ID) AS ( SELECT EMPLOYEE_ID AS E_ID, FIRST_NAME AS E_NAME, MANAGER_ID AS MGR_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMP_ID UNION ALL SELECT EMPLOYEES.EMPLOYEE_ID AS E_ID, EMPLOYEES.FIRST_NAME AS E_NAME, EMPLOYEES.MANAGER_ID AS MGR_ID FROM EMPLOYEES JOIN EMPLOYEECTE ON EMPLOYEES.EMPLOYEE_ID = EMPLOYEECTE.MGR_ID
)
Select E1.FIRST_NAME, NVL(E2.E_NAME, 'No Boss') AS MANAGER_NAME
FROM EMPLOYEECTE E1 LEFT JOIN EMPLOYEECTE E2 ON E1.MANAGER_ID = E2.E_ID;

Cheers!!

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.