With clause query looking for solution-CodePudding

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;

fiddle