I have a not working query and I cant find find the problem, can someone help me solve it, for me seem to be fine, but I get error all of the time.
WITH cte_name as
(select EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
MANAGER_ID,1 as HIERARCHY_LEVEL
from hr.employees
union all
select emp.EMPLOYEE_ID,
emp.FIRST_NAME,
emp.LAST_NAME,
emp.MANAGER_ID,
cte.HIERARCHY_LEVEL 1 as HIERARCHY_LEVEL
from hr.EMPLOYEES emp
inner JOIN cte_name cte
on emp.MANAGER_ID = cte.EMPLOYEE_ID
)
select *
from cte_name;
CodePudding user response:
The error you get is:
ORA-32039: recursive WITH clause must have column alias list
Which is self-explanatory; you need to include the column alias list:
WITH cte_name (employee_id, first_name, last_name, manager_id, hierarchy_level) as (
select EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
MANAGER_ID,
1
from hr.employees
union all
select emp.EMPLOYEE_ID,
emp.FIRST_NAME,
emp.LAST_NAME,
emp.MANAGER_ID,
cte.HIERARCHY_LEVEL 1
from hr.EMPLOYEES emp
inner JOIN cte_name cte
on emp.MANAGER_ID = cte.EMPLOYEE_ID
)
select *
from cte_name;