Recursive SQL returning only 1 row

Transact-SQL
,
1

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

2

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.

3

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

4

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.

5

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

1 Like
6

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

7

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

8

Is this on Oracle or on SQL Server??

9

oracle

10

Transact-SQL is SQL Server.

Oracle is PL/SQL.

11

as @ScottPletcher suggested

12

thank you @yosiasz @ScottPletcher

13

hi VasuK

hope this helps

the result set is ordered by Manager

create emp table script

drop table if exists #Emp
create table #Emp ( empnum int , ENAME varchar(20) , manager int )
insert into #Emp select 7839,'KING ',null
insert into #Emp select 7698,'BLAKE ',7839
insert into #Emp select 7782,'CLARK ',7839
insert into #Emp select 7566,'JONES ',7839
insert into #Emp select 7654,'MARTIN ',7698
insert into #Emp select 7499,'ALLEN ',7698
insert into #Emp select 7844,'TURNER ',7698
insert into #Emp select 7900,'JAMES ',7698
insert into #Emp select 7521,'WARD ',7698
insert into #Emp select 7902,'FORD ',7566
insert into #Emp select 7369,'SMITH ',7902
insert into #Emp select 7788,'SCOTT ',7566
insert into #Emp select 7876,'ADAMS ',7788
insert into #Emp select 7934,'MILLER ',7782

;WITH Employee_CTE(empnum,ename,manager) AS  
(  
   SELECT empnum,ename,manager FROM #Emp WHERE empnum=7839 
       UNION ALL  
   SELECT e.empnum,e.ename,e.manager FROM #Emp e INNER JOIN Employee_CTE c ON e.manager= c.empnum   
)  
SELECT 
     * 
FROM 
    Employee_CTE 
ORDER BY 
     manager

image

image

14

hi VasuK

the error you are getting

do you know how to DEBUG ? .. anyhow .. here are two articles