Building a Recursive View | CREATE RECURSIVE VIEW | Teradata Vantage - Building a Recursive View - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The basic definition of a recursive view consists of these component classes.
  • A non-recursive, or seed query class.
  • A recursive query class.
  • A UNION ALL set operator to connect the query components.
  • A terminal condition to prevent infinite recursion.

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.

Syntax

CREATE RECURSIVE VIEW view_name [(column_name [,...])] AS
  (query_expression_1 UNION ALL query_expression_2)

Syntax Elements

view_name
The name by which the view is referenced.
column_name
The name of a column in the base table.
query_expression_1
A seed statement.
query_expression_2
A recursive statement.

Example

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);

The view_name is reachable_from.

The base table is flights. The view has only base table columns source, destination, and depth.

query_expression_1 is:

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

This is the seed statement (see Seed Statement Component of the View Definition) for the recursive view definition. The statement does not reference any recursive relations at any point in its definition.

query_expression_2 is:

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

This is the recursive statement (see Recursive Statement Component of the View Definition) for the recursive view definition. The recursion occurs because the view name reachable_from is referenced in the FROM clause of this statement.