Building a Recursive View | CREATE RECURSIVE VIEW | Teradata Vantage - Building a Recursive View - Advanced SQL Engine - Teradata Database

A recursive view definition can contain multiple seed and recursive statements.

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.

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:

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

The following example is based on the Flights table defined in The Concept of Recursion.

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.

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

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