recursion - Oracle - recursive with

Question

I am learning recursive with in Oracle. Based on HR Schema from https://github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql i wanted to create query, which will create a tree of employee-manager.

I've created this query:

with cte(empno,ename,mgr,lvl) as (
  SELECT empno,
         ename,
         mgr,
         0
  FROM   emp
  union all
  SELECT c.empno,
         c.ename,
         c.mgr,
         c.lvl + 1
  FROM   cte c
  join   emp p
         on c.mgr = p.empno
  WHERE p.empno = 7369
)
select * from cte;

But it's no working. What am I missing? Is there a good tutorial, or someone would explain to me how RECURSIVE WITH works?

Answer 1

Note that your attempt is based on the SCOTT schema, not on HR.

One simple mistake is where you put the "starting" employee number. That should go in the "anchor" branch (the first branch of UNION ALL), not the "recursive" branch. That is similar to START WITH vs. CONNECT BY in a hierarchical query (connect-by query).

The other mistake, also very simple but very significant, is in the recursive branch. You want to select additional rows from p, you don't want to repeat the values that already existed in c. (That mistake also explains why you ran into "cycle error" - you were repeatedly selecting the same data, over and over again.) The correct solution is to fix that in the query - not to use the CYCLE clause with the wrong query. CYCLE is an advanced feature and you shouldn't need it at this stage; if you run into "cycle error" you are very likely making a different mistake somewhere in the code.

Notice the change from c. ... to p. ... below.

with cte(empno,ename,mgr,lvl) as (
  SELECT empno,
         ename,
         mgr,
         0
  FROM   scott.emp
  WHERE empno = 7369        --  Moved here
  union all
  SELECT p.empno,           --  Not c.empno!  Same in the next two lines.
         p.ename,
         p.mgr,
         c.lvl + 1
  FROM   cte c
  join   scott.emp p
         on c.mgr = p.empno
)
select * from cte;



     EMPNO ENAME             MGR        LVL
---------- ---------- ---------- ----------
      7369 SMITH            7902          0
      7902 FORD             7566          1
      7566 JONES            7839          2
      7839 KING                           3