17.10 - Breadth First Reporting - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

You can simulate a BFS result by ordering the final result by the depth of the recursion. For example, consider the following view definition.

    CREATE RECURSIVE VIEW reachable_from (destin, cost, depth) AS (
      SELECT root.destin, root.cost, 0 AS depth
      FROM flights AS root
      WHERE root.source = 'paris'
    UNION ALL
      SELECT result.destin, seed.cost+result.cost, seed.depth+1 AS depth
      FROM reachable_from AS seed, flights AS result
      WHERE seed.destin = result.source
      AND   depth <= 20);

By ordering the query result on the depth variable, the following SELECT request provides a breadth first report on this view.

    SELECT *
    FROM reachable_from
    ORDER BY depth;