/***************************************/
/* ORACLE RECURSIVE                    */
/* Counting up by +1                   */
/* Using CTE (Common Table Expression) */
/***************************************/

select 1 as n from dual
union all 
select 2 from dual  
union all 
select 3 from dual;


/* Create a CTE using WITH ... */

WITH countUp as 
  (select 1 as n from dual 
   union all
   select 2 from dual  
   union all
   select 3 from dual 
  )
select * from countUp;


/* Defining the column name on the WITH statement */
/* instead of on the first select statement       */ 

WITH countUp (n) as 
  (select 1 from dual  
   union all
   select 2 from dual  
   union all
   select 3 from dual 
  )
select * from countUp;


/* Creating a recursive CTE (Common Table Expression) */

WITH countUp (n) as 
  (select 1 from dual                           /* start with */ 
   union all
   select n+1 from countUp                      /* use recursion */ 
   where n < 5
  ) 
select * from countUp;


/* Creating a factrorial CTE */

WITH factorial (n, factValue) as 
  (select 1, 1 from dual 
   union all
   select n+1, (n+1)*factValue
   from factorial 
   where n < 5
  ) 
select * from factorial;


/* Navigating thru a hierarchy tree */

WITH hierTree (employee_id, fname, lname, manager_id) AS
   (SELECT employee_id, fname, lname, manager_id
    FROM hierarchy 
    WHERE manager_id IS NULL
  UNION ALL
    SELECT h.employee_id, h.fname, h.lname, h.manager_id
    FROM hierarchy h
    JOIN hierTree  ht ON h.manager_id = ht.employee_id
  )
SELECT * FROM hierTree;