WITH Statement Modifier
Purpose
Enables you to search a table using iterative self-join and set operations. Multiple definitions of the nonrecursive form can be used with a single SELECT statement by specifying different queries by name.
Note: You can only use this statement modifier with SELECT statements. WITH cannot be used with other DML statements.
Syntax
where:
|
Syntax Element … |
Specifies … |
|
Nonrecursive Form The WITH statement modifier is similar to a derived table. It defines a named query list from which the SELECT statement can select data. |
|
|
query_name |
the name of the nonrecursive query. |
|
column_name |
the name of a column in the named query definition. |
|
select_expression |
a nonrecursive SELECT statement that retrieves the row data to store in the named query. You can specify an expression that returns a UDT in a column list only if its transform group has a fromsql routine. The system automatically converts the expression from its UDT value to the external type via the fromsql routine before returning it to a client application. See “Specifying UDTs in an SQL Request” on page 36 for details. You can specify a row-level security constraint column in the select list of a SELECT request. However, the column cannot be specified as part of an arithmetic expression in the select list. The value returned for the column is the coded value for the row-level security constraint from the row. |
|
Recursive Form The WITH RECURSIVE statement modifier defines a named query that can refer to itself in the query definition and in the SELECT statement that it precedes. The named query list consists of at least one nonrecursive, or seed, statement and at least one recursive statement. You can invoke a scalar UDF within the WITH RECURSIVE definition of a recursive query. The rules are the same as those for invoking a scalar UDF within a SELECT request. |
|
|
query_name |
the name of the recursive query. |
|
column_name |
the name of a column in the named query definition. |
|
UNION ALL |
the operator that adds results of iterative operations to the named query. Because UNION ALL permits duplicate rows, it is not a true relational operator. |
|
Seed Statement The seed statement is a nonrecursive SELECT statement that retrieves row data from other tables to store in the named query. |
|
|
DISTINCT |
that only one row is to be returned from any set of duplicates that might result from a given expression list. Two rows are considered duplicates only if each value in one is equal to the corresponding value in the other. |
|
ALL |
that all rows, including duplicates, are to be returned in the results of the expression list. This is the default value. |
|
* |
that all columns of all tables referenced in the FROM clause of the seed statement be returned. When qualified by table_name, specifies that all columns of table_name only are to be returned. |
|
expression |
any valid SQL expression, including scalar UDFs. |
|
AS |
an optional introduction to expression_alias_name. |
|
expression_alias_name |
an alias for the expression. |
|
table_name |
the name of a table, derived table, or view. table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause. |
|
FROM |
an introduction to the names of one or more tables, views, or derived tables from which expression is to be derived. The FROM clause in a seed statement cannot specify the TABLE option. |
|
Single Table This option enables the FROM clause of the seed statement to specify single tables. A FROM clause can include a sequence of single table references, which creates an implicit inner join. A FROM clause in a seed statement cannot specify the name of the WITH RECURSIVE query. |
|
|
table_name |
the name of a single table, derived table, table UDF, or view referred to in the FROM clause. |
|
AS |
an optional introduction to correlation_name. |
|
correlation_name |
an alias for the table referenced in the FROM clause. |
|
Joined Tables Options for joined tables enable the FROM clause of the seed statement to specify that multiple tables be joined in explicit ways, as described below. |
|
|
joined_table |
the name of a joined table. |
|
INNER |
a join in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition. Inner join is the default join type. |
|
LEFT OUTER |
an outer join with the table that was listed first in the FROM clause. In a LEFT OUTER JOIN, the rows from the left table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls. |
|
RIGHT OUTER |
an outer join with the table that was listed second in the FROM clause. In a RIGHT OUTER JOIN, the rows from the right table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls. |
|
FULL OUTER |
that rows be returned from both tables. In a FULL OUTER JOIN, rows from both tables that have not been returned in the result of the inner join will be returned in the outer join result and extended with nulls. |
|
JOIN |
an introduction to the name of the second table to participate in the join. |
|
ON search_condition |
one or more conditional expressions that must be satisfied by the result rows. An ON condition clause is required if the FROM clause specifies an outer join. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
|
CROSS JOIN |
an unconstrained or Cartesian join; it returns all rows from all tables specified in the FROM clause. |
|
Derived Tables The derived table option enables the FROM clause of a seed statement to specify a spool file made up of selected data from an underlying table set. The derived table acts like a viewed table. |
|
|
(subquery) |
nested SELECT statements. SELECT AND CONSUME statements cannot be used in a subquery. You can specify NORMALIZE in a subquery. |
|
AS |
an optional introduction to derived_table_name. |
|
derived_table_name |
the name of the derived table. |
|
column_name |
the column name. This field is for the column name only. Do not use forms such as Tablename.Columnname or Databasename.Tablename.Columnname. Columns with a UDT type are valid with some exceptions. See “Specifying UDTs in an SQL Request” on page 36 for the specific exceptions. |
|
WHERE |
an introduction to the search condition in the seed statement. |
|
search_condition |
a conditional search expression that must be satisfied by the row or rows returned by the seed statement. If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form. |
|
GROUP BY |
an introduction to the specification of how to group result rows. |
|
ordinary_group_set |
a column expression by which the rows returned by the seed statement are grouped. The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type. ordinary_grouping_set falls into three general categories: See “Ordinary Grouping Set Expressions” on page 146 for definitions. |
|
empty_grouping_set |
a contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. This syntax is used to request a grand total of the computed group totals. |
|
rollup_list |
a ROLLUP expression that reports result rows in a single dimension with one or more levels of detail. The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type. |
|
cube_list |
a CUBE expression that reports result rows in multiple dimensions with one or more levels of detail. The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type. |
|
grouping_sets_ |
a GROUPING SETS expression that reports result rows in one of two ways: |
|
HAVING |
an introduction to the conditional clause in the SELECT statement. |
|
search_condition |
one or more conditional expressions that must be satisfied by the result rows. If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form. |
|
QUALIFY |
an introduction to a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major difference between QUALIFY and HAVING is that QUALIFY filtering is based on the result of performing various ordered analytical functions on the data. |
|
search_condition |
one or more conditional expressions that must be satisfied by the result rows. If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form. |
|
ORDER BY |
the order in which result rows are to be sorted. |
|
expression |
an expression in the SELECT expression list of the seed statement, either by name or by means of a constant that specifies the numeric position of the expression in the expression list. |
|
column_name |
the names of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending. See “SELECT” on page 12. |
|
column_name_alias |
a column name alias specified in the select expression list of the query for the column on which the result rows are to be sorted. If you specify a column_name_alias to sort by, then that alias cannot match the name of any column that is defined in the table definition for any table referenced in the FROM clause of the query whether that column is specified in the select list or not. This does not work because the system always references the underlying physical column having the name rather than the column that you attempt to reference using that same name as its alias. If you attempt to specify such a column_name_alias, the system aborts the request and returns an error. The workaround for this is to specify the sort column by its column_position value within the select list for the query. See “Rules and Restrictions for the ORDER BY Clause” on page 223. |
|
column_position |
the numeric position of the columns specified by the ORDER BY clause. These can be ascending or descending. |
|
ASC |
that the results are to be ordered in ascending sort order. If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session. The default order is ASC. |
|
DESC |
that the results are to be ordered in descending sort order. If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session. |
|
Recursive Statement The recursive statement is a SELECT statement that retrieves row data from a join of the named query and other tables. You cannot include NORMALIZE in a recursive statement of a recursive query. |
|
|
* |
that all columns of all tables referenced in the FROM clause of the recursive statement be returned. When qualified by table_name, specifies that all columns of table_name only are to be returned. |
|
expression |
any valid SQL expression, with these exceptions: |
|
AS |
an optional introduction to expression_alias_name. |
|
expression_alias_name |
an alias for the expression. |
|
table_name |
the name of the named query or the name of a table or view. table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause. |
|
FROM |
an introduction to the named query and one or more tables or views from which expression is to be derived. The FROM clause in a recursive statement cannot specify the TABLE option. |
|
Implicit Join This option enables the FROM clause of the recursive statement to specify the name of the WITH RECURSIVE query and one or more single table references, creating an implicit inner join. |
|
|
query_name |
the named query referred to in the FROM clause. |
|
AS |
an optional introduction to correlation_name. |
|
correlation_name |
an alias for the query name referenced in the FROM clause. |
|
table_name |
the name of a single table or view referred to in the FROM clause. |
|
AS |
an optional introduction to correlation_name. |
|
correlation_name |
an alias for the table name referenced in the FROM clause. |
|
Explicit Join Options for joined tables enable the FROM clause of the seed statement to specify that multiple tables be joined in explicit ways, described as follows. |
|
|
query_name |
the named query referred to in the FROM clause. |
|
join_table_name |
the name of a joined table. |
|
INNER |
a join in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition. Inner join is the default join type. |
|
LEFT OUTER |
an outer join with the table that was listed first in the FROM clause. In a LEFT OUTER JOIN, the rows from the left table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls. |
|
RIGHT OUTER |
an outer join with the table that was listed second in the FROM clause. In a RIGHT OUTER JOIN, the rows from the right table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls. |
|
JOIN |
an introduction to the name of the second table to participate in the join. |
|
joined_table |
the name of the joined table. |
|
ON search_condition |
one or more conditional expressions that must be satisfied by the result rows. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. An ON condition clause is required if the FROM clause specifies an outer join. |
|
WHERE Clause |
|
|
WHERE |
an introduction to the search condition in the recursive statement. |
|
search_condition |
a conditional search expression that must be satisfied by the row or rows returned by the recursive statement. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
ANSI Compliance
The WITH statement modifier is ANSI SQL:2011 compliant.
Other SQL dialects support similar non‑ANSI standard statements with names such as:
Nonrecursive WITH Statement Modifier
The nonrecursive WITH statement modifier is similar to a derived table. A derived table defines a named temporary result set from which the query can select data. Similarly, nonrecursive WITH defines a temporary named result set from which the SELECT statement can select data.
Consider these table definitions:
CREATE TABLE product (
product_id INTEGER,
on_hand INTEGER);
CREATE TABLE stocked (
store_id INTEGER,
product_id INTEGER,
quantity INTEGER);
The following statement uses a nonrecursive WITH statement modifier to define a temporary named result set called orderable_items that is built from the select expression that follows the AS keyword:
WITH orderable_items (product_id, quantity) AS
( SELECT stocked.product_id, stocked.quantity
FROM stocked, product
WHERE stocked.product_id = product.product_id
AND product.on_hand > 5
)
SELECT product_id, quantity
FROM orderable_items
WHERE quantity < 10;
The same results are produced by this statement using a derived table:
SELECT product_id, quantity
FROM (SELECT stocked.product_id, stocked.quantity
FROM stocked, product
WHERE stocked.product_id = product.product_id
AND product.on_hand > 5) AS orderable_items
WHERE quantity < 10;
WITH RECURSIVE Statement Modifier
The WITH RECURSIVE statement modifier provides a way to iteratively query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.
WITH RECURSIVE has three execution phases:
1 Create an initial nonrecursive, or seed, result set.
2 Recurse the intermediate result sets based on the seed result set until no new rows are added to the temporary named result set.
3 Execute a last query on the temporary named result set to return the final result set.
Consider this employee table:
CREATE TABLE employee (
employee_number INTEGER,
manager_employee_number INTEGER,
last_name CHARACTER(20),
first_name VARCHAR(30));
The table represents an organizational structure of employee-manager relationships. The employee table is similar to this organization chart.
The following recursive query retrieves the employee numbers of all employees who directly or indirectly report to the manager who has an employee_number value of 801:
WITH RECURSIVE temp_table (employee_number) AS
(SELECT root.employee_number
FROM employee AS root
WHERE root.manager_employee_number = 801
UNION ALL
SELECT indirect.employee_number
FROM temp_table AS direct, employee AS indirect
WHERE direct.employee_number = indirect.manager_employee_number
)
SELECT *
FROM temp_table
ORDER BY employee_number;
In the example, temp_table is a temporary named result set that can be referred to in the FROM clause of the recursive statement.
The initial result set is established in temp_table by the nonrecursive, or seed, statement and contains the employees that report directly to the manager with an employee_number of 801:
SELECT root.employee_number
FROM employee AS root
WHERE root.manager_employee_number = 801
The recursion takes place by joining each employee in temp_table with employees who report to the employees in temp_table. The UNION ALL adds the results to temp_table.
SELECT indirect.employee_number
FROM temp_table AS direct, employee AS indirect
WHERE direct.employee_number = indirect.manager_employee_number
Recursion stops when no new rows are added to temp_table.
The final query is not part of the recursive WITH request modifier and extracts the employee information from temp_table:
SELECT *
FROM temp_table
ORDER BY employee_number;
The results of the recursive query are as follows:
employee_number
---------------
1001
1002
1003
1004
1006
1008
1010
1011
1012
1014
1015
1016
1019
WITH and WITH RECURSIVE Statement Modifiers
The rules and restrictions are:
Because UNION ALL permits duplicate rows, it is not a true set operator or a true relational operator. Mathematical sets cannot contain duplicates.
This request produces the same results as the request that specifies a nonrecursive WITH statement modifier in “Nonrecursive WITH Statement Modifier” on page 69:
WITH RECURSIVE orderable_items (product_id, quantity) AS (
SELECT stocked.product_id, stocked.quantity
FROM stocked, product
WHERE stocked.product_id = product.product_id
AND product.on_hand > 5)
SELECT product_id, quantity
FROM orderable_items
WHERE quantity < 10;
Rules and Restrictions for Embedded SQL
The rules and restrictions are:
Using a WITH Statement Modifier Result as Input to a Table Function in the FROM Clause
You can specify the temporary result set created by a WITH statement modifier subquery as input to a FROM clause table function. See “Example 2: WITH Statement Modifier Result Set as Input to a Table Function” on page 77.
Depth Control to Avoid Infinite Recursion
If the data hierarchy is cyclic, or if the recursive statement specifies a bad join condition, a recursive query can produce a request that never completes with a finite result.
In this context, a bad join is defined as a join that contains one or more of these errors.
Consider the employee table defined in “WITH RECURSIVE Statement Modifier” on page 70.
The following statement specifies an incorrect join condition in the recursive statement. The join condition (WHERE indirect.employee_number IN (1003, 1004) is not correct because the result is always true.
WITH RECURSIVE temp_table (employee_id, level) AS (
SELECT root.employee_number, 0 AS level
FROM employee AS root
WHERE root.employee_number = 1003
UNION ALL
SELECT direct.employee_id, direct.level + 1 ←recursive statement
FROM temp_table AS direct, employee AS indirect
WHERE indirect.employee_number IN (1003,1004)
)
SELECT *
FROM temp_table
ORDER BY level;
The result set returned by this query is as follows:
employee_id level
----------- -----
1003 0
1003 1
1003 1
1003 2
1003 2
1003 2
1003 2
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
1003 3
... ...
and so on infinitely.
The best practice is to control the depth of the recursion as follows:
The following example adds a join condition (AND direct.level < 2) to the recursive statement in the previous query to limit the number of levels of recursion.
WITH RECURSIVE temp_table (employee_id, level) AS (
SELECT root.employee_number, 0 AS level
FROM employee AS root
WHERE root.employee_number = 1003
UNION ALL
SELECT direct.employee_id, direct.level+1
FROM temp_table AS direct, employee AS indir
WHERE indir.employee_number IN (1003,1004)
AND direct.level < 2
)
SELECT *
FROM temp_table
ORDER BY level;
Note that the data type of the numeric literal that you specify for the initial value of the depth control column is the smallest data type that can contain the value.
For example, in the preceding query, the data type of the numeric literal 0 is BYTEINT because it is the smallest type that can fit the value 0.
The data type of the initial value of the depth control column limits the number of levels of recursion to the maximum value that the data type can represent.
For example, the maximum value of a BYTEINT is 127. If you need more than 127 levels of recursion, you must cast the numeric literal that you specify for the initial value of the depth control column to a larger type.
Example : Multiple Seed and Recursive Statements
This example shows a recursive query that uses multiple seed and recursive statements.
Consider these two tables:
CREATE TABLE planes (
depart VARCHAR(40),
arrive VARCHAR(40),
carrier VARCHAR(40),
cost DECIMAL(5,0));
CREATE TABLE trains (
depart VARCHAR(40),
arrive VARCHAR(40),
cost DECIMAL(5,0));
The data in the planes table is as follows:
|
Depart |
Arrive |
Carrier |
Cost |
|
Paris |
New York |
AA |
199 |
|
Paris |
London |
AA |
99 |
|
London |
New York |
AA |
199 |
|
New York |
Mexico City |
UA |
99 |
|
Mexico City |
New York |
UA |
99 |
|
Paris |
Mexico City |
AF |
299 |
|
New York |
London |
AA |
199 |
|
New York |
Tokyo |
JAL |
999 |
|
Mexico City |
Tokyo |
JAL |
999 |
|
Tokyo |
New York |
JAL |
999 |
The data in the trains table is as follows:
|
Depart |
Arrive |
Cost |
|
Paris |
London |
99 |
|
London |
Paris |
99 |
|
Paris |
Milan |
199 |
|
London |
Milan |
199 |
|
Milan |
Paris |
199 |
|
Milan |
Rome |
49 |
|
Rome |
Florence |
49 |
The following query uses two seed statements and two recursive statements to return all cities reachable from Paris by train or plane.
WITH RECURSIVE temp_table (depart, arrive, carrier, depth) AS (
SELECT p_root.depart, p_root.arrive, p_root.carrier, 0 AS depth
FROM planes p_root
WHERE p_root.depart = 'Paris'
UNION ALL
SELECT t_root.depart, t_root.arrive, 'EuroRail', 0 AS depth
FROM trains t_root
WHERE t_root.depart = 'Paris'
UNION ALL
SELECT direct.depart, indirect.arrive, indirect.carrier, direct.depth+1
FROM temp_table AS direct, planes AS indirect
WHERE direct.arrive = indirect.depart
AND indirect.arrive <> 'Paris'
AND direct.depth <= 4
UNION ALL
SELECT direct.depart, indirect.arrive, 'EuroRail', direct.depth+1
FROM temp_table AS direct, trains AS indirect
WHERE direct.arrive = indirect.depart
AND indirect.arrive <> 'Paris'
AND direct.depth <= 4)
)
SELECT DISTINCT arrive (TITLE 'Destinations Reachable From Paris')
FROM temp_table;
The result set for this recursive query is as follows:
Destinations Reachable From Paris
----------------------------------------
Florence
London
Mexico City
Milan
New York
Rome
Tokyo
Example : WITH Statement Modifier Result Set as Input to a Table Function
Suppose you have created these tables and table function.
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER);
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER);
CREATE FUNCTION add2int (
a INTEGER,
b INTEGER)
RETURNS TABLE (addend1 INTEGER,
addend2 INTEGER,
mysum INTEGER)
SPECIFIC add2int
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!add3int!add2int.c';
Use the temporary result set derived from the subquery in the WITH statement modifier as input to table function add2int in the FROM clause.
WITH dt(a,b) AS (
SELECT a1, b1
FROM t1)
SELECT addend1, addend2, mysum
FROM dt, TABLE (add2int(dt.a, dt.b)) AS tf
ORDER BY 1,2,3;
Example : Specifying a Dynamic UDT in a Recursive Query
The following example shows how you can use dynamic UDTs in a recursive query.
WITH MyDerived(u_sal) AS (
SELECT NEW MP_STRUCTURED_INT(salary, '1', '1') AS u_sal
FROM employee)
SELECT udf_aggr_avg_mp_struc(NEW VARIANT_TYPE(1 AS dummy,
u_sal AS x) )
FROM MyDerived;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
udf_aggr_avg_mp_struc(NEW VARIANT_TYPE (dummy, x))
--------------------------------------------------
33438
Example : External UDF Examples
The following examples all show various simple ways to specify scalar or table external UDFs in a WITH or WITH RECURSIVE statement modifier.
In this request, the input to the external UDF is recursive, and the UDF is joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, addend2, mysum,d+1
FROM dt,table (add2int(dt.a,dt.b)) AS tf
WHERE d < 2
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is not recursive, and the UDF is joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, addend2, mysum,d+1
FROM dt,table (add2int(t1.a1,t1.b1)) AS tf
WHERE d < 2
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is recursive, and the UDF is not joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, r.b1, mysum, 1 AS d
FROM table (add2int(dt.a,dt.b)) tf, t1 r
WHERE d < 1
AND tf.addend1=t1.a1
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is not recursive, and the UDF is not joined with a recursive table.
WITH dt(a,b,c) AS (
SELECT a1, b1 ,a1-b1
FROM t1
UNION ALL
SELECT addend1, addend2, mysum
FROM table (add2int(t1.a1, t1.b1)) tf
)
SELECT *
FROM dt;
Example : Invoking an SQL UDF in the WITH RECURSIVE Statement Modifier
This example invokes the SQL UDF value_expression in the WHERE clause of the WITH RECURSIVE statement modifier.
WITH RECURSIVE temp_table (employee_number) AS (
SELECT root.employee_number
FROM employee AS root
WHERE root.manager_employee_number = 801
AND test.value_expression(dept_no, 0) = 25;
UNION ALL
SELECT indirect.employee_number
FROM temp_table AS direct, employee AS indirect
WHERE direct.employee_number = indirect.manager_employee_number
AND test.value_expression(2,3) = 5
)
SELECT *
FROM temp_table
ORDER BY employee_number;
For More Information
For more information on recursive queries and views, see: