missing keyword error in oracle, when I tried the concept of with-clause in CTE

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;

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!!