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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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:

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