CREATE/REPLACE RECURSIVE VIEW Examples | Teradata Vantage - Example: Controlling Infinite Recursion - Advanced SQL Engine - Teradata Database

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'
    UNION ALL
      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.