[teiid-issues] [JBoss JIRA] (TEIID-3501) IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"

[Juraj Duráni (JIRA)]

Juraj Duráni created TEIID-3501:
-----------------------------------

             Summary: IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"
                 Key: TEIID-3501
                 URL: https://issues.jboss.org/browse/TEIID-3501
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 8.7.1.6_2
         Environment: teradata version: 15
            Reporter: Juraj Duráni
            Assignee: Steven Hawkins


The teradata DB does not support CAST statement in IN. Query (1), teradata exception (2), source specific command (3), query that teradata accepts (4).

(1) SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimeStampValue IN (convert('2000-01-01 00:00:12.0', timestamp), convert('2000-01-01 00:00:45.0', timestamp)) ORDER BY IntKey

(2) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN (cast('2000-01-01 00:00:12.0' AS TIMESTAMP(6)), cast('2000-01-01 00:00:45.0' AS TIMESTAMP(6))) ORDER BY 1

(3) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN ('2000-01-01 00:00:12.0', '2000-01-01 00:00:45.0') ORDER BY 1

(4) Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.05] [Error 3706] [SQLState 42000] Syntax error: expected something between ')' and ','.
	at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:308)
...




--
This message was sent by Atlassian JIRA
(v6.3.15#6346)