15.00 - Recursive Statement Component of the View Definition - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Recursive Statement Component of the View Definition

A recursive statement is the recursive statement portion of a recursive view definition. Its purpose is to cycle through recursively, building the eventual answer set. Unlike the seed statement component, the recursive statement component always references a recursive relation at least once, and possibly many times.

Taking the example developed in “The Concept of Recursion” on page 483, the code highlighted in bold typeface is the recursive statement for the definition.

    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 in1, flights AS out1
      WHERE in1.destination = out1.source
      AND   in1.depth <= 100);

As you can see, there are references to the recursive view being defined, reachable_from, in the FROM clause of this statement as well as in its WHERE clause, in which the column names source, destination, and depth are referenced.

All forms of negation and all forms of aggregation are banned from recursive statements to ensure that monotonicity is never violated (see the description of fixpoint semantics in “The Concept of Recursion” on page 483).