15.00 - You Can Specify Multiple Seed and Recursive Statements Within a Single Recursive View Definition - 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)

You Can Specify Multiple Seed and Recursive Statements Within a Single Recursive View Definition

As mentioned briefly in “Building a Recursive View” on page 489, you can specify multiple seed and recursive statements within a single recursive view definition. The only restrictions for multiple seed or recursive statements within a recursive view definition are the following.

  • The first statement specified must be a seed statement.
  • Otherwise, the system returns an error to the requestor.

  • There is no limit on the number of seed or recursive statements you can specify within a single recursive view definition.
  • Note that you are limited by the maximum SQL text size for the system, which is 1 megabyte.

    When you define a recursive view that contains multiple seed or recursive statements, the system evaluates the entire seed statement set at the beginning of the operation and then evaluates the entire recursive statement set on each iteration cycle, using the rows from the previous iteration as input.

    As an example, consider the following example view definition, which is used to find all cities that can be reached from the German city of Kaiserslautern either by train or by plane.

    CREATE RECURSIVE VIEW tc (source, destination, carrier, depth) AS (
      SELECT f.source, f.destination, f.carrier, 0 AS depth
      FROM flights AS f                                       -- Query_1
      WHERE f.source = 'Kaiserslautern'
    UNION ALL
       (SELECT r.source, r.destination, 'EuroRail', 0 AS depth
        FROM trains AS r                                      -- Query_2
        WHERE r.source = 'Kaiserslautern'
    UNION ALL
        SELECT tcq.source, f.destination, f.carrier, tcq.depth + 1
        FROM tc AS tcq, flights AS f                          -- Query_3
        WHERE tcq.destination=f.source
        AND   tcq.depth <= 100
    UNION ALL
        SELECT tcq.source, r.destination, 'EuroRail', teq.depth + 1
        FROM tc AS tcq, trains AS r                           -- Query_4
        WHERE tcq.destination = r.source
        AND   tcq.depth <= 100 ) );

    In this example, Query_1 and Query_2 are both seed statements, while Query_3 and Query_4 are both recursive statements. The parentheses used to segregate Query_1 from the other 3 queries are used to clarify the relationships, not to enforce execution order. Specifying parentheses within the recursive query do not affect execution order because the UNION ALL set operator is both associative and commutative.

    Having defined the recursive view named tc, all you need to do to answer the question “find all cities that can be reached from the Kaiserslautern either by train or by plane” is to run the following simple SELECT statement.

        SELECT *
        FROM tc;