bizarre CAST/CASE problem

[mcdunna]

This problem is driving me batty:

This query works:
SELECT a.tran_nbr, b.a_nbr, CAST(a.tran_end_tmstp AS time(2) with time zone) as tran_endtime, b.sid_cd
FROM APROD_VIEW_DB.transaction AS a INNER JOIN APROD_VIEW_DB.air_information AS b ON a.tran_nbr = b.a_tran_nbr

This one works as well:
SELECT a.tran_nbr, b.a_nbr, CASE WHEN left(b.sid_cd, 2) = '04' THEN 'I' ELSE 'D' END AS intl_domestic
FROM APROD_VIEW_DB.transaction AS a INNER JOIN APROD_VIEW_DB.air_information AS b ON a.tran_nbr = b.a_tran_nbr

But this one does not:
SELECT a.tran_nbr, b.a_nbr, CAST(a.tran_end_tmstp AS time(2) with time zone) as tran_endtime, CASE WHEN left(b.sid_cd, 2) = '04' THEN 'I' ELSE 'D' END AS intl_domestic
FROM APROD_VIEW_DB.transaction AS a INNER JOIN APROD_VIEW_DB.air_information AS b ON a.tran_nbr = b.a_tran_nbr

I am using the Teradata SQL assistant tool on windows and I get an error number 3706 "Syntax Error: Expected something between the 'WHEN' keyword and the 'left' keyword".

What on earth is the problem here? I'm stumped.

Thanks for any assistance.

Andrew

[NetFx]

Did you try SubStr(b.sid_cd, 1, 2)?

[dnoeth]

Hi Andrew,
LEFT is not a Teradata/Standard SQL function, it's ODBC-SQL.
If it worked before then because Options -> Query -> "Allow Use of ODBC SQL Extensions in Queries" was checked. As it's not recommended to use those functions (like LTRIM, CONCAT, full list in QDBC manual) better use SUBSTRING as Shaw suggested.

Dieter

[mcdunna]

Sweet! That was indeed the solution. I used "substr" instead of "left" and it works fine now. Thanks very much for the assistance!

Andrew