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:
- Create an initial nonrecursive, or seed, result set.
- Recurse the intermediate result sets based on the seed result set until no new rows are added to the temporary named result set.
- 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:
- The only set operator that can appear within a WITH RECURSIVE request modifier is UNION ALL.
Because UNION ALL permits duplicate rows, it is not a true set operator or a true relational operator. Mathematical sets cannot contain duplicates.
- The following elements cannot appear within a WITH or WITH RECURSIVE statement modifier:
- WITH or WITH RECURSIVE statement modifier
- TOP n operator
- User-defined functions
- The following elements cannot appear within a recursive statement in a WITH RECURSIVE statement modifier:
- NOT IN or NOT EXISTS logical predicate
- Aggregate functions
- Ordered analytical functions
- GROUP BY clause
- HAVING clause
- DISTINCT clause
- Subqueries
- Derived tables
- You cannot specify a WITH or WITH RECURSIVE statement modifier in the definitions of any of these database objects:
- Views and recursive views
- Triggers
- Stored procedures
- Derived tables
- A WITH RECURSIVE statement modifier that does not have a recursive statement works like a nonrecursive WITH statement modifier.
This request produces the same results as the request that specifies a nonrecursive WITH statement modifier:
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
Teradata Database does not support recursive queries for these forms of embedded SQL:
- Static embedded SQL
- Dynamic embedded SQL
These statements cannot be preceded within a WITH or WITH RECURSIVE statement modifier:
- SELECT … INTO
- DECLARE CURSOR
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: WITH Statement Modifier Result Set as Input to a Table Function.
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.
- Joining incorrect columns.
- Selecting the wrong columns from the join.
- Specifying an OR operator instead of an AND operator with multiple join conditions.
- Specifying a join condition that is always true.
Consider the employee table defined in WITH RECURSIVE Statement Modifier.
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:
- Specify a depth control column in the column list of the WITH RECURSIVE statement modifier.
- Initialize the column value to 0 in the seed statement.
- Increment the column value by 1 in the recursive statement.
- Specify a limit for the value of the depth control column in the join condition of the recursive statements.
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.