Database
Fan

## Recursive Query Column length

Hi,

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 length0 as depthfrom UI_RESULTS_DB.yz_865617_flightswhere 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,

Ben

Tags (3)
2 REPLIES
Junior Contributor

## 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.

Fan

## Re: Recursive Query Column length

Thank you, Dieter!