If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

A common table expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows.
Multiple common table expressions can be specified following the single WITH keyword.
Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.

...
...

The common table expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements.

A common table expression can be used:

•In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used)
•To enable grouping by a column that is derived from a scalar subselect or function that is not deterministic or has external action
•When the desired result table is based on host variables
•When the same result table needs to be shared in a fullselect
•When the result needs to be derived using recursion
•When multiple SQL data change statements need to be processed within the query

If the fullselect of a common table expression contains a reference to itself in a FROM clause,
the common table expression is a recursive common table expression.
Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.

The following must be true of a recursive common table expression:

...
...

When developing recursive common table expressions, remember that an infinite recursion cycle (loop) can be created.
Check that recursion cycles will terminate. This is especially important if the data involved is cyclic.
A recursive common table expression is expected to include a predicate that will prevent an infinite loop.
The recursive common table expression is expected to include:

•In the iterative fullselect, an integer column incremented by a constant.
•A predicate in the where clause of the iterative fullselect in the form "counter_col < constant" or "counter _col < :hostvar".

A warning is issued if this syntax is not found in the recursive common table expression (SQLSTATE 01605).