[teiid-issues] [JBoss JIRA] (TEIID-3223) An issue with time in WHERE clause - SQLServer

[Juraj Duráni (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3223?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Juraj Duráni reopened TEIID-3223:
---------------------------------


There is a problem with older SQL Server 2005. I want to cast the source (SQLS) data type 'datetime' to the teiid data type 'time' (dynamic VDB):
<metadata type="DDL"><![CDATA[
				CREATE VIEW smalla_view(
					DateValue date,
					TimeValue time,
					TimestampValue timestamp
				) AS SELECT
					convert(SmallA.DateValue, date) AS DateValue,
					convert(SmallA.TimeValue, time) AS TimeValue,
					SmallA.TimestampValue
				FROM SmallA;
				CREATE FOREIGN TABLE SmallA (
					INTNUM integer,
					BIGDECIMALVALUE bigdecimal,
					BIGINTEGERVALUE biginteger,
					BOOLEANVALUE boolean,
					BYTENUM byte,
					CHARVALUE char,
					DATEVALUE timestamp,
					DOUBLENUM double,
					FLOATNUM float,
					INTKEY integer,
					LONGNUM long,
					OBJECTVALUE object,
					SHORTVALUE short,
					STRINGKEY string,
					STRINGNUM string,
					TIMESTAMPVALUE timestamp,
					TIMEVALUE timestamp
				) OPTIONS(UPDATABLE 'TRUE');
			]]> </metadata>

But the SQL query results in an exception:
SELECT TimeValue FROM BQT1.smalla_view WHERE TimeValue <= {t '21:00:00'}  ==>  Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 cast('1970-01-01 ' + convert(varchar, g_0.TIMEVALUE, 8) AS datetime) AS c_0 FROM SmallA g_0 WHERE cast('1970-01-01 ' + convert(varchar, g_0.TIMEVALUE, 8) AS datetime) <= cast('21:00:00' as time)]'. Originally TeiidProcessingException 'Type time is not a defined system type.' SQLServerException.java:196.


> An issue with time in WHERE clause - SQLServer
> ----------------------------------------------
>
>                 Key: TEIID-3223
>                 URL: https://issues.jboss.org/browse/TEIID-3223
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.7.1
>         Environment: OS: fedora20
> arch: x86_64
> java: oracle 1.7
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>             Fix For: 8.7.1, 8.10
>
>
> Teiid is not able to manage WHERE clause with time value.
> translator: sqlserver
> base DB: SQL Server 2012/2008/2005



--
This message was sent by Atlassian JIRA
(v6.3.11#6341)