common-table-expression

A common table expression defines a result table with table-identifier that can be referenced in any FROM clause of the fullselect that follows.

Multiple common table expressions can be specified following the single WITH keyword. Each specified common table expression can also be referenced by name in the FROM clause of subsequent common table expressions.

common-table-expression


>>-table-identifier--+---------------------------+--AS---------->
                     |    .-,---------------.    |       
                     |    V                 |    |       
                     '-(----+-------------+-+--)-'       
                            '-column-name-'              

>--(fullselect)------------------------------------------------><

If a list of columns is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If these column names are not specified, the names are derived from the select list of the fullselect used to define the common table expression.

table-identifier must be an unqualified SQL identifier, and it must be different from any other table-identifier in the same statement. If the common table expression is specified in an INSERT statement, the table-identifier must not be the same as the table or view name that is the object of the insert. If the common table expression is specified in a CREATE VIEW statement, the table-identifier must not be the same as the view name that is created. A common table expression table-identifier can be specified as a table name in any FROM clause throughout the fullselect.

If more than one common table expression is defined in the same statement, cyclic references between the common table expressions are not permitted. A cyclic reference occurs when two common table expressions dt1 and dt2 are created such that dt1 refers to dt2 and dt2 refers to dt1. Furthermore, a common table expression defined before cannot refer to subsequent common table expressions.

A common table expression name can only be referenced in the select-statement, SELECT INTO statement, INSERT statement, CREATE VIEW statement, or RETURN statement that defines it.

If a select-statement, SELECT INTO statement, INSERT statement, or CREATE VIEW statement that is not contained in a trigger definition refers to a unqualified table name, the following rules are applied to determine which table is actually being referenced:

If the unqualified name corresponds to one or more common table expression names that are specified in the select-statement, the name identifies the common table expression that is in the innermost scope.
Otherwise, the name identifies a persistent table, a temporary table, or a view that is present in the default schema.

If a select-statement, SELECT INTO statement, INSERT statement, or CREATE VIEW statement that is contained in a trigger definition refers to a unqualified table name, the following rules are applied to determine which table is actually being referenced:

If the unqualified name corresponds to one or more common table expression names that are specified in the select-statement, the name identifies the common table expression that is in the innermost scope.
If the unqualified name corresponds to a transition table name, the name identifies that transition table.
Otherwise, the name identifies a persistent table, a temporary table, or a view that is present in the default schema.

The common table expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements. However, the use of common table expressions is not allowed in a INSERT within SELECT statement.

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)
When the result table that you want 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

If a 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:

Each fullselect that is part of the recursion cycle must start with SELECT or SELECT ALL. Use of SELECT DISTINCT is not allowed. Furthermore, the set operators must use the ALL keyword.
The column names must be specified following the table-name of the common table expression.
The first fullselect of the first set operator (the initialization fullselect) must not include a reference to the common table expression itself in any FROM clause).
If a column name of the common table expression is referred to in the iterative fullselect, the data type, length, and CCSID for the column are determined based on the initialization fullselect. The corresponding column in the iterative fullselect must have the same data type and length as the data type and length determined based on the initialization fullselect and the CCSID must match. However, for character string types, the length of the two data types can differ. In this case, the column in the iterative fullselect must have a length that would always be assignable to the length determined from the initialization fullselect. If a column of a recursive common table expression is not used recursively in its definition, the data type, length, and CCSID for the column are determined by applying rules associated with non-recursive queries.
Each fullselect that is part of the recursion cycle must not include any aggregate functions, GROUP BY clauses, HAVING clauses, ORDER BY clauses, or FETCH FIRST clauses. The FROM clauses of these fullselects can include at most one reference to a common table expression that is part of a recursion cycle.
Subqueries (scalar or quantified) must not be part of any recursion cycles.
Outer join must not be part of any recursion cycles.

Start of change All columns referenced in a recursive common table expression are nullable for the query, even if they are defined as NOT NULL. End of change

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 of "counter_col < constant" or "counter_col < :hostvar". A warning is issued if this syntax is not found.

Start of change If the result of a recursive common table expression is used to derive the final result table, and if a column mask is used to mask the column values in the final result table, the column mask cannot be applied to a column that is specified in the fullselect of the recursive common table expression. End of change