Recursive Query Column length


Recursive Query Column length


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
from UI_RESULTS_DB.yz_865617_flights
where orig ='LAX'

union all

select all_trips.orig, flights.dest, all_trips.cost + flights.cost,
trim(all_trips.route) ||'->'||flights.dest ,
all_trips.depth +1
from all_trips
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, 


Senior Apprentice

Re: Recursive Query Column length

Hi Ben,

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.


Re: Recursive Query Column length

Thank you, Dieter!