Nonrecursive Named Query in a WITH Modifier - Teradata Database - Teradata Vantage NewSQL Engine

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;

RECURSIVE Named Query in a WITH Modifier

A recursive named query provides a way to iteratively query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy.

A recursive query cannot reference another recursive query, either directly or indirectly.

A recursive named query 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 Modifiers