Teradata Recursive View

Here is a complete example of building a recursive view in Teradata.

The table stores records that show the current [slightly simplified] hierarchy of two top-level automobile manufacturers.

Table Create

CREATE MULTISET TABLE AutoMarque (
    MarqueName        VARCHAR(200)  NULL,
    ParentMarqueName  VARCHAR(200)  NULL
) UNIQUE PRIMARY INDEX AutoMarque_UPI (MarqueName);


Inserts

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen AG',                  'Volkswagen AG');
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('AUDI AG',                        'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bentley Motors Limited',         'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bugatti Automobiles S.A.S.',     'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('SEAT, S.A.',                     'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Skoda automobilova a.s.',        'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen Passenger Cars',      'Volkswagen AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('NSU Motorenwerke AG',            'AUDI AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Automobili Lamborghini S.p.A.',  'AUDI AG'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Tata Motors',                    'Tata Motors'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Jaguar',                         'Tata Motors'); 
INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Land Rover',                     'Tata Motors');

Recursive View Definition

REPLACE RECURSIVE VIEW AutoMarqueRecurse (
    MarqueName,
    ParentMarqueName,
    TopLevelMarqueName,
    RecurseLevel
) AS LOCKING ROW FOR ACCESS
SELECT
    MarqueName,
    ParentMarqueName,
    MarqueName,  -- TopLevelMarqueName
    1
FROM AutoMarque
WHERE ParentMarqueName = MarqueName

UNION ALL

SELECT
    m.MarqueName,
    m.ParentMarqueName,
    r.TopLevelMarqueName,
    r.RecurseLevel + 1 
FROM AutoMarqueRecurse AS r
JOIN AutoMarque        AS m
  ON m.ParentMarqueName = r.MarqueName 
WHERE m.ParentMarqueName <> m.MarqueName;

SELECT All Rows From Base Table

MarqueName                    ParentMarqueName
----------------------------- ---------------- 
Automobili Lamborghini S.p.A. AUDI AG
NSU Motorenwerke AG           AUDI AG
Jaguar                        Tata Motors
Land Rover                    Tata Motors
Tata Motors                   Tata Motors
AUDI AG                       Volkswagen AG
Bentley Motors Limited        Volkswagen AG
Bugatti Automobiles S.A.S.    Volkswagen AG
SEAT, S.A.                    Volkswagen AG
Skoda automobilova a.s.       Volkswagen AG
Volkswagen AG                 Volkswagen AG
Volkswagen Passenger Cars     Volkswagen AG

SELECT All Rows From Recursive View

MarqueName                    ParentMarqueName TopLevelMarqueName RecurseLevel 
----------------------------- ---------------- ------------------ --------------
Jaguar                        Tata Motors      Tata Motors        2
Land Rover                    Tata Motors      Tata Motors        2
Tata Motors                   Tata Motors      Tata Motors        1
Automobili Lamborghini S.p.A. AUDI AG          Volkswagen AG      3
NSU Motorenwerke AG           AUDI AG          Volkswagen AG      3
AUDI AG                       Volkswagen AG    Volkswagen AG      2
Bentley Motors Limited        Volkswagen AG    Volkswagen AG      2
Bugatti Automobiles S.A.S.    Volkswagen AG    Volkswagen AG      2
SEAT, S.A.                    Volkswagen AG    Volkswagen AG      2
Skoda automobilova a.s.       Volkswagen AG    Volkswagen AG      2
Volkswagen AG                 Volkswagen AG    Volkswagen AG      1
Volkswagen Passenger Cars     Volkswagen AG    Volkswagen AG      2