I am new to the recursive query. I was practicing on some sample data collected from the Internet. Here is my code:
CREATE volatile table flights (orig char(3) not null, dest char(3) not null, cost int)
on commit preserve rows;
INSERT INTO flights VALUES ('LAX', 'BOS', 300);
INSERT INTO flights VALUES ('LAX', 'SFO', 100);
INSERT INTO flights VALUES ('SFO', 'CHI', 275);
INSERT INTO flights VALUES ('CHI', 'BOS', 180);
INSERT INTO flights VALUES ('LAX', 'ATL', 250);
INSERT INTO flights VALUES ('ATL', 'BOS', 140);
with recursive all_trips ( orig, dest, cost,route , depth ) as (
select orig, dest,cost,
orig||'->'||dest||' ' as route, ----the space is used to define the length
0 as depth
where orig ='LAX'
select all_trips.orig, flights.dest, all_trips.cost + flights.cost,
trim(all_trips.route) ||'->'||flights.dest ,
inner join flights on all_trips.dest = flights.orig
and all_trips.orig ='LAX' --necessary ?
where depth <2 )
select * from all_trips order by depth
Question 1: as you can the route column is to show all the connections should it is not a direct flight. If i run it without all the spaces in the seed query party, all the content of route field resulted from recursive portion would be truncated. I AM WONDERING IF THERE IS MORE ELEGANT WAY TO HANDLE THIS?
Question 2: in the recurcise portion, the query I got from the Internet has "all_trips.orig = "LAX". WONDERING THIS IS NECESSARY? IF SO, WHAT KIDN OF FUNCTION?
Thanks a lot,
in Teradata the first Select of a UNION determines the resulting data type, you need to CAST to a larger VarChar like "CAST(orig||'->'||dest AS VARCHAR(100))".
The "all_trips.orig ='LAX'" in the recursive part is useles, in fact it's no recursion anymore as yo probably don't fly from LAX to LAX.