All Forums Database
cqhollon 1 post Joined 08/16
25 Aug 2016
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"

dnoeth 4628 posts Joined 11/04
25 Aug 2016

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"

 

Dieter

You must sign in to leave a comment.