16.20 - view_name - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Name of the recursive view to be created or replaced.

For information about naming database objects, see Teradata Vantage™ SQL Fundamentals, B035-1141.

If view_name is not fully qualified, the default database is used.

database_name
user_name
Containing database or user for view_name if something other than the current database or user.
column_name
Mandatory name of a view column or column set. If more than one column is specified, list their names in the order in which each is to be displayed for the view.
AS
An introduction to the view definition.

Example: A Simple Recursive View

This example uses the following base table as its base to build a simple recursive review definition with a counter to control the possibility of infinite recursion:

partlist
Part Subpart Quantity
00 01 5
00 05 3
01 02 2
01 03 3
01 04 4
02 05 7
02 06 6

This view is designed to answer questions such as the following: Which parts are required to build part 01?

Notice that as written, this query does not really answer the question because there might be more than 100 levels in the data. Strictly speaking, the question this request asks is this: Which parts, up to a maximum of 100 levels, are required to build part 01?

The recursive view definition is as follows:

    CREATE RECURSIVE VIEW rpl (part, subpart, quantity, depth) AS (
      SELECT root.part, root.subpart, root.quantity, 0 AS depth
      FROM partlist AS root
      WHERE root.part = '01'
    UNION ALL
      SELECT child.part, child.subpart, child.quantity, parent.depth + 1
      FROM rpl AS parent, partlist AS child
      WHERE parent.subpart = child.part
      AND   parent.depth <= 100);

The query to answer the question of which parts are required to build part 01 is the following SELECT request:

    SELECT part, subpart, quantity
    FROM rpl
    ORDER BY part, subpart, quantity;

The result set for this query is the following.

Part Subpart Quantity
   01    02    2
   01    03    3
   01    04    4
   02    05    7
   02    06    6

Example: Simple REPLACE RECURSIVE VIEW

The following example replaces the existing definition for the recursive view named rec with the specified SQL code:

    REPLACE RECURSIVE VIEW rec(p, mycount) AS (
      SELECT n, 0 AS mycount
      FROM t
      WHERE n = 1
    UNION ALL
      SELECT rec.p, rec.mycount + 1
      FROM t, rec
      WHERE rec.p = t.n
      AND   rec.mycount <= 20