All Forums Database
zyxhrb 3 posts Joined 03/16
18 Mar 2016
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, 

dnoeth 4628 posts Joined 11/04
20 Mar 2016

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.


zyxhrb 3 posts Joined 03/16
21 Mar 2016

Thank you, Dieter!

You must sign in to leave a comment.