## Thursday, July 28, 2011

### 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

```