Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.10

Aster
Lei
Fan

Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.10

SELECT T0."foo" AS "foo",T0."Derive1" AS "Derive1"
FROM (SELECT TOP 500 T0."foo" AS "foo",EXTRACT(YEAR FROM {fn TIMESTAMPADD(SQL_TSI_HOUR, 0, CURRENT_TIMESTAMP)}) AS "Derive1" FROM (SELECT T0."foo" AS "foo" FROM (select 1 as foo) T0) T0) T0

 

[2017-01-30 16:11:24] SQL Validation Error: 42000[-3704] [Teradata][ODBC Teradata Driver][Teradata Database] '{' ('7B'X) is not a valid Teradata SQL token. 

  • native ODBC driver
Tags (1)

Accepted Solutions
Teradata Employee

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1

You are attempting to apply the database function EXTRACT to the result of a client-side ODBC scalar function TIMESTAMPADD. The "legacy parser" which was the default prior to 15.10 went to great lengths to try to translate this sort of technically incorrect mix into a valid SQL statement. The newer, more efficient, parser does not.

 

EXTRACT(YEAR FROM (CURRENT_TIMESTAMP + INTERVAL '0' HOUR)) would be the TD SQL equivalent (though I don't see much point in adding zero hours).

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1

You are attempting to apply the database function EXTRACT to the result of a client-side ODBC scalar function TIMESTAMPADD. The "legacy parser" which was the default prior to 15.10 went to great lengths to try to translate this sort of technically incorrect mix into a valid SQL statement. The newer, more efficient, parser does not.

 

EXTRACT(YEAR FROM (CURRENT_TIMESTAMP + INTERVAL '0' HOUR)) would be the TD SQL equivalent (though I don't see much point in adding zero hours).

Lei
Fan

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1

Thanks a lot. It does help.

Enthusiast

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1

Hi, can you point us to the relevant part of the Teradata documentation that mentions that we should not use the database function EXTRACT for scalar functions like TIMESTAMPADD from 15.10 onwards? Just so that we can understand what else we should look into in our code.

 

thanks,
Nicolas

Teradata Employee

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1

There is no specific statement in the documentation about this case. The ODBC Driver Reference does discuss deprecated features and new parser versus "legacy" parser. 

 

You can open an incident to see if the behavior of the new parser can be changed to allow this usage, or post to the "Connectivity" forum.

Enthusiast

Re: Same SQL, got error with Teradata native ODBC driver 15.10/16.00, but ok with native driver 14.1