15.00 - Depth First Reporting - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Depth First Reporting

You can simulate a DFS result by ordering the final result by the path of the recursion. The path in this context means how a row is found through recursive iterations. In this example, the path is the concatenation of the city names.

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