15.00 - Building a Recursive View - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Building a Recursive View

As mentioned briefly in “The Concept of Recursion” on page 483, the basic definition of a recursive view consists of 4 component classes.

  • A non‑recursive, or seed query class.
  • A recursive query class.
  • A UNION ALL set operator to connect the query components.
  • A terminal condition to prevent infinite recursion.
  • Normally, you should specify the terminal condition explicitly in the WHERE clause of the recursive statement, but such an explicit condition is not mandatory because recursive queries are implicitly limited by limits on user spool space and system disk space.

    A recursive view definition can contain multiple seed and recursive statements. This definition is minimal.

    Consider the following basic BNF (BNF, or Backus-Naur Form, is a metalanguage used to define computer languages) for the CREATE RECURSIVE VIEW statement.

         CREATE RECURSIVE VIEW <view_name> [(<column_name_list>)] AS (
         <query_expression_1> UNION ALL <query_expression_2>

    where:

     

    Syntax element …

    Specifies …

    <view_name>

    the name by which the view is referenced.

    <column_name_list>

    a set of base table column names and expressions from which the view referenced by <view_name> is to be constructed.

    <query_expression_1>

    a seed statement.

    <query_expression_2>

    a recursive statement.

    Angle bracket characters (<>) indicate an identifier or complex syntax element and straight bracket characters ([]) indicate an optional syntax component.

    Now consider the following example CREATE RECURSIVE VIEW statement, which is based on the Flights table defined in “The Concept of Recursion” on page 483.

        CREATE RECURSIVE VIEW reachable_from (source,destination,depth) AS (
          SELECT root.source, root.destination, 0 AS depth
          FROM flights AS root
          WHERE root.source = 'Paris'
        UNION ALL
          SELECT in1.source, out1.destination, in1.depth + 1
          FROM reachable_from AS in1, flights AS out1
          WHERE in1.destination = out1.source
          AND   in1.depth <= 100);

    How does this example related to the BNF for CREATE RECURSIVE VIEW? The units of the BNF correspond to this example recursive view definition as follows.

     

    BNF element …

    Corresponds to this example query element …

    view_name

    reachable_from

    column_name_list

    source, destination, depth

    query_expression_1

    SELECT root.source, root.destination, 0 AS depth
    FROM flights AS root
    WHERE root.source = 'Paris'

    This is the seed statement (see “Seed Statement Component of the View Definition” on page 491) for the recursive view definition. The statement does not reference any recursive relations at any point in its definition.

    query_expression_2

    SELECT in1.source,out1.destination,in1.depth + 1
    FROM reachable_from AS in1, flights AS out1
    WHERE in1.destination = out1.source
    AND   in1.depth <=100

    This is the recursive statement (see “Recursive Statement Component of the View Definition” on page 492) for the recursive view definition. The recursion occurs because the view name reachable_from is referenced in the FROM clause of this statement.