Recursive WITH (simple factorial)

Here's a simple example of a 'Recursive WITH' clause.

When declaring a recursive WITH clause, you need to specify the variables that will be used in the sub-query (in essence, the columns that will be generated by the sub-query). This is done in parenthesis immediately after the name of the sub-query.

The sub-query consists of two queries connected by a "UNION ALL". The first query initializes the column values -- the first row of values. The second query then uses the previous row of values to calculate the next row of values. This continues while the WHERE clause of the second query is true.

The main query simply selects the values and the rows from the results of the recursive WITH as any regular query would select results from a sub-query.

In this example of a recursive WITH sub-query, the "n" column is simply a counter and the process is terminated when it reaches "8". The "val" column is generated by taking the previous "n" value, adding one to it then multiplying by the previous "val" value. The result is a table of numbers with their factorial values (n!).

/* ===== code ===== */  
                                                         
     WITH  
       factorial ( n, val )  as
         ( /*=== The Recursive WITH ===*/
           select  1 as n,  1 as val  
             from  dual
           UNION ALL 
           select  n+1 as n,  (n+1)*val as val
             from  factorial
            where  n < 8
         )
     select  n
          ,  val
       from  factorial
      order  by  n 
     ;
             
                                                                                  
/* --- Results --- */    
                                                         
     --  N   VAL   
     --  --  ------ 
     --  1   1     
     --  2   2     
     --  3   6     
     --  4   24    
     --  5   120   
     --  6   720   
     --  7   5040  
     --  8   40320