Seed Statement Component of the View Definition | VantageCloud Lake - Components Supported in a View Definition - Teradata Vantage

Seed Statement Component of the View Definition

A seed statement is the non-recursive statement portion of a recursive view definition. Its purpose is to provide the initial row set to be used to build a recursive relation later in the process. To produce the desired recursion, the seed statement must produce 1 or more rows; otherwise, running the recursive query produces an empty result set.

By definition, there are no references to recursive relations anywhere in the seed statement. Taking the example developed in The Concept of Recursion, the bold code is the seed 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);

There are no references to recursive relations within the seed query.

Recursive Statement Component of the View Definition

The recursive statement component in a recursive view definition runs recursively, building the eventual answer set. Unlike the seed statement component, the recursive statement component references a recursive relation at least once, and possibly many times.

Taking the example developed in The Concept of Recursion, the bold code 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);

There are references to the recursive view being defined, reachable_from, in the FROM clause of this statement and 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 make sure that monotonicity is never violated (see the description of fixpoint semantics in The Concept of Recursion).

Multiple Seed and Recursive Statements in a Single Recursive View Definition

You can specify multiple seed and recursive statements within a single recursive view definition. The first statement specified must be a seed statement.

Although you can specify any number of seed or recursive statements within a single recursive view definition, the maximum SQL text size for the system is 1 megabyte.

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. See Building a Recursive View.

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;