17.10 - Recursive Statement Component of the View Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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, 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).