SQL error using Teradata JDBC

Database
Enthusiast

SQL error using Teradata JDBC

Has anybody run into this where the JDBC driver acts inconsistenly. We understand the inconsistency but there does not seem to be a pattern to the inconsistency. All we are trying to find out is the inconsistent pattern. We are TD 14.10 and using the 15.00 driver. Shown the query below. This works fine when using Teradata studio with the same driver I am using and also works fine with SQLA using ODBC and .NET connections. The bolded line is the problem. If we switch the Cast and Upper it works fine. We have a similar column from another table with the exact same column datatype (VARCHAR(255)) which works. There is nothing in this column which is different than the other column where it works on another table. Same database too and same connection strings. 

SELECT DISTINCT AP.PATT_ID,

  CASE

    WHEN AP.PATT_TYPE_CD = 'TMP'

    THEN NULL

    ELSE AP.INV_TYPE_CD

  END AS CNTL_INV_TYPE_CD,

  AP.USER_ID,

  AP.PATT_NM,

  AP.PATT_DESC,

  AP.PATT_TYPE_CD,

  AP.CREATE_DT,

  PO.PATT_TEMPLATE AS PATT_TEMPLATE

FROM ADM_PATT AP

JOIN PATT_REL PR

ON AP.PATT_ID = PR.PATT_ID

LEFT OUTER JOIN PATT_OPTIONS PO

ON AP.PATT_ID      = PO.PATT_ID

WHERE AP.STATUS_CD = 'ACTIVE'

AND CAST(AP.USER_ID AS      VARCHAR(255)) LIKE 'SYSTEM'

AND CAST(PR.REL_TYPE AS     VARCHAR(255)) LIKE 'ML'

AND CAST(AP.PATT_TYPE_CD AS VARCHAR(255)) LIKE '%%'

AND (CAST(PO.PATT_TEMPLATE AS VARCHAR(255)) LIKE '%%'

OR PO.PATT_TEMPLATE IS NULL)

AND CAST(UPPER(AP.PATT_DESC) AS VARCHAR(255)) LIKE '%%'

AND TO_DATE(TO_CHAR(AP.CREATE_DT, 'MM/DD/YYYY'), 'MM/DD/YYYY') BETWEEN TO_DATE('01/01/1900', 'MM/DD/YYYY') AND TO_DATE('12/31/2099', 'MM/DD/YYYY')

ORDER BY AP.PATT_ID ASC

4 REPLIES
Enthusiast

Re: SQL error using Teradata JDBC

The error is :

SQL state [HY000]; error code [3536]; [Teradata Database] [TeraJDBC 15.00.00.20] [Error 3536] [SQLState HY000] UPPERCASE or CASESPECIFIC specified for non-CHAR data.; nested exception is java.sql.SQLException: [Teradata Database] [TeraJDBC 15.00.00.20] [Error 3536] [SQLState HY000] UPPERCASE or CASESPECIFIC specified for non-CHAR data.

Teradata Employee

Re: SQL error using Teradata JDBC

Error 3536 is a Teradata Database error, not a Teradata JDBC Driver error, so this is not likely to be due to "inconsistent behavior" from the Teradata JDBC Driver.

It is more likely that you see different behavior when you use a different Transaction Mode, TMODE=ANSI versus TMODE=TERA.

Enthusiast

Re: SQL error using Teradata JDBC

NOTE that this SQL works on SQL and Teradata Express but not through the JDBC driver and JNDI.

Teradata Employee

Re: SQL error using Teradata JDBC

You said that you are trying to find the reason for the behavior difference.

As I mentioned already, the behavior difference is likely due to you using different transaction modes in the successful cases versus the unsuccessful case.

You can execute the HELP SESSION command to determine which transaction mode you are using.