Problem with Java Driver

General

Problem with Java Driver

Hi All, I hope im setting this in the right place.

Well I've a problem. I'm trying to run a query through Java I've the driver and everything.. i'm able to run other queries but i've problems when ccame to this one:

Select Po_Ln_Itm_Net_Prc_Dc_Amt,Po_Ln_Itm_Net_Prc_Unit_Qty
FROM
EDW.PRCRMNT_SPEND_SAVING_DTL
WHERE
MTRL_ID = '06ASA10518D271'
and PO_NUM is not null
and (Spend_Saving_Dt >= CAST( YEAR(CURRENT_DATE) || '-01-01' AS DATE) AND Prcrmnt_Spend_Saving_Dtl.Spend_Saving_Dt <= CURRENT_DATE)
and Buypoint_Vndr_Acct_Num = '1008979003'
;

When i run it with the SQL Assistant it does work just fine,

but i run the same query through Java and i get this error:

Select exception ignored: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 13.10.00.01] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the 'YEAR' keyword.
com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 13.10.00.01] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the 'YEAR' keyword.
Error code: 3706
SQL State: 42000
Message: [Teradata Database] [TeraJDBC 13.10.00.01] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the 'YEAR' keyword.
Localized Message: [Teradata Database] [TeraJDBC 13.10.00.01] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the 'YEAR' keyword.

Any idea why i could be getting this error??

please help me

Well thanks in advice for your help.
1 REPLY
Teradata Employee

Re: Problem with Java Driver

>>> When i run it with the SQL Assistant it does work just fine

SQL Assistant uses the Teradata ODBC Driver, which parses your SQL request and rewrites it. The Teradata ODBC Driver implements the YEAR function.

In contrast, the Teradata JDBC Driver does not parse or rewrite your SQL request. Instead, the Teradata JDBC Driver sends your unchanged SQL request to the Teradata Database.

The Teradata Database does not provide a YEAR function. You need to change your query to use the SQL syntax that is supported by the Teradata Database. Specifically, you must use the EXTRACT(YEAR FROM...) function.

In your query, you must replace

YEAR(CURRENT_DATE)

with

EXTRACT(YEAR FROM CURRENT_DATE)

Your entire query should look like the following:

Select Po_Ln_Itm_Net_Prc_Dc_Amt,Po_Ln_Itm_Net_Prc_Unit_Qty
FROM
EDW.PRCRMNT_SPEND_SAVING_DTL
WHERE
MTRL_ID = '06ASA10518D271'
and PO_NUM is not null
and (Spend_Saving_Dt >= CAST( EXTRACT(YEAR FROM CURRENT_DATE) || '-01-01' AS DATE) AND Prcrmnt_Spend_Saving_Dtl.Spend_Saving_Dt <= CURRENT_DATE)
and Buypoint_Vndr_Acct_Num = '1008979003'

Please note that a trailing semicolon is NOT needed when using the Teradata JDBC Driver.