17.05 - Example: Multiple Seed and Recursive Statements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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:

Depart Arrive Carrier Cost
Paris New York AA 199
Paris London AA 99
London New York AA 199
New York Mexico City UA 99
Mexico City New York UA 99
Paris Mexico City AF 299
New York London AA 199
New York Tokyo JAL 999
Mexico City Tokyo JAL 999
Tokyo New York JAL 999

The data in the trains table is as follows:

Depart Arrive Cost
Paris London 99
London Paris 99
Paris Milan 199
London Milan 199
Milan Paris 199
Milan Rome 49
Rome Florence 49

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