Case statement syntax change?


Case statement syntax change?

I recently upgraded to Teradata SQL assistant v15.10 and teh database is version 14.00. All of a sudden I get a syntax error on this statement

the error is  Failed 3706 Syntax error: expected something between the 'when' keyword and the 'year' keyword

select a.sourcesystemid
sum(case when year(a.pnrcreatedate)= 2016 and month(a.pnrcreatedate) = 01 then 1 else 0 end) as "Jan 2016"

Senior Apprentice

Re: Case statement syntax change?

There's no YEAR or MONTH function in Teradata, this is (deprecated) ODBC-syntax, which might be automatically replaced with valid SQL by the ODBC driver. In older releases of SQL Assistant there was an option "allow use of ODBC SQL extensions in queries", in SQLA 15.10 & ODBC you have to configure your ODBC source: uncheck "Options->Disable Parsing" and check "Options->EnableLegacyParser".

A better solution is to use valid Teradata SQL instead:

,sum(case when extract(year from a.pnrcreatedate)= 2016 and extract(month from a.pnrcreatedate) = 01 then 1 else 0 end) as "Jan 2016"

Or better do it without calculation:

,sum(case when a.pnrcreatedate between date '2016-01-01' and date '2016-01-31' then 1 else 0 end) as "Jan 2016"