SQLTeam.com | Weblogs | Forums

Recursive SQL returning only 1 row

Hi,

The Below recursive query is returning only one row , i am trying to find the manager hierarchies of all the employees, as part of this initially i tried the below query, its returning only the result of the first query (only 1 row ), could some one please help me in fixing this to get the result of all rows.

WITH  emp_cte (empnum,ename,manager) AS (
  SELECT emp.empno,emp.ename, cast(emp.mgr as varchar(20)) from emp where emp.mgr is null
  union all
  select emp_cte.empnum,  emp_cte.ename, emp.ename from emp_cte join emp on  
  emp_cte.manager = cast(emp.empno as varchar(20)) 
  ) 
SELECT * from emp_cte

when I execute the above query , getting the below result only one row, but not getting the all rows with their managers

|EMPNUM|ENAME|MANAGER|
|7839 | KING| - |

Thanks In advance

Welcome!

change

emp_cte.manager = cast(emp.empno as varchar(20)) 

to

emp_cte.empnum= cast(emp.manager as varchar(20)) 

but I am not sure about all of the CASTing stuff.

Hi Yosiasz

Thank your for the reply, as you suggested I changed the query as below

WITH  emp_cte (empnum,ename,manager) AS (
  SELECT emp.empno,emp.ename, cast(emp.mgr as varchar(20)) from emp where emp.mgr is null
  union all
  select emp_cte.empnum,  emp_cte.ename, emp.ename from emp_cte join emp on  
  **emp_cte.empnum= emp.mgr**  
  ) 
SELECT * from emp_cte

now I am getting the below error when I execute

ORA-32044: cycle detected while executing recursive WITH query

This is very confusing. You don't clearly state what value is present in the emp.mgr column. Is it empno or ename?

You are not getting results now because emp.mgr is NULL in the first row(s), and NULL will never be = to any other value, not even another NULL.

This is a SQL Server forum. You would likely get better results from an Oracle forum.

1 Like

You stated SQL 2012 then gave an Oracle error message??

emp.mgr is numeric column with employee number of the manager (eg : 7456)

Is this on Oracle or on SQL Server??

oracle

Transact-SQL is SQL Server.

Oracle is PL/SQL.

as @ScottPletcher suggested

thank you @yosiasz @ScottPletcher