ANSI Compliance - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

The WITH statement modifier is ANSI SQL:2011 compliant.

Other SQL dialects support similar non‑ANSI standard statements with names such as:

  • CONNECT BY PRIOR
  • 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;

    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

    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 in “Nonrecursive WITH Statement Modifier” on page 53:

         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;

    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
  • 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 60.

    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” on page 54.

    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.