15.10 - Example: Controlling Infinite Recursion - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

The following example uses the flights table to indicate a method for limiting the possibility of infinitely recursive processing of cyclic data:

    CREATE RECURSIVE VIEW reachable_from (destination, cost, depth) AS (
      SELECT root.destination, root.cost, 0 AS depth
      FROM flights AS root
      WHERE root.source = 'Paris'
      SELECT out.destination, in.cost + out.cost, in.depth + 1 AS depth
      FROM reachable_from AS in, flights AS out
      WHERE in.destination = out.source
      AND   in.depth <= 20);

This recursive view is written to be queried by the following SELECT request.

    SELECT *
    FROM reachable_from;

In this example, the variable depth is used as a counter, initialized to 0 within the seed query for the recursive view definition and incremented by 1 within the recursive query for the definition.

The AND condition of the WHERE clause then tests the counter to ensure that it never exceeds a value of 20. Because the depth counter was initialized to 0, this condition limits the recursion to 21 cycles.