Example: Multiple Seed and Recursive Statements - Analytics Database - Teradata Vantage

This example shows a recursive query that uses multiple seed and recursive statements.

Consider these two tables:

    CREATE TABLE planes (
      depart  VARCHAR(40),
      arrive  VARCHAR(40),
      carrier VARCHAR(40),
      cost    DECIMAL(5,0));

    CREATE TABLE trains (
      depart VARCHAR(40),
      arrive VARCHAR(40),
      cost   DECIMAL(5,0));

The data in the planes table is as follows:

The data in the trains table is as follows:

The following query uses two seed statements and two recursive statements to return all cities reachable from Paris by train or plane.

     WITH RECURSIVE temp_table (depart, arrive, carrier, depth) AS (
       SELECT p_root.depart, p_root.arrive, p_root.carrier, 0 AS depth         
       FROM planes p_root
       WHERE p_root.depart = 'Paris'
     UNION ALL
       SELECT t_root.depart, t_root.arrive, 'EuroRail', 0 AS depth             
       FROM trains t_root
       WHERE t_root.depart = 'Paris'
     UNION ALL
       SELECT direct.depart, indirect.arrive, indirect.carrier, direct.depth+1 
       FROM temp_table AS direct, planes AS indirect
       WHERE direct.arrive = indirect.depart
       AND   indirect.arrive <> 'Paris'
       AND   direct.depth <= 4
     UNION ALL
       SELECT direct.depart, indirect.arrive, 'EuroRail', direct.depth+1       
       FROM temp_table AS direct, trains AS indirect
       WHERE direct.arrive = indirect.depart
       AND   indirect.arrive <> 'Paris'
       AND   direct.depth <= 4)
       )
     SELECT DISTINCT arrive (TITLE 'Destinations Reachable From Paris')
     FROM temp_table;

The result set for this recursive query is as follows:

     Destinations Reachable From Paris
     ----------------------------------------
     Florence
     London
     Mexico City
     Milan
     New York
     Rome
     Tokyo