teiid How to use BETWEEN keyword for TIMESTAMP in oracle database query

[kulbhushanc]

Hi,

I am trying to execute between keyword with timestamp but teiid throwing parsing exception.

Query sample:

SELECT SQL_REDO FROM "V$LOGMNR_CONTENTS" where SEG_OWNER=HARSHS and OPERATION IN("INSERT","UPDATE","DELETE") and TABLE_NAME=SOURCE_DM_DATA'' and TIMESTAMP BETWEEN TO_DATE ("2017-01-19T19:22:03", "YYYY-MM-DD"T"HH24:MI:SS") AND TO_DATE(2017-01-19T19:22:24, YYYY-MM-DD"T"HH24:MI:SS);

Note : I am using teiid 8.13

Thanks,

Kulbhushan Chaskar.

[shawkins]

What is your exception?

Unless you have registered a UDF/source function to handle TO_DATE, there isn't a function known by that name to Teiid.  For timestamp literals you can use a JDBC escape - {ts '2017...'}, an ansi keyword - TIMESTAMP '2017...', or the parseTimestamp function.  For the escape or the keyword the string format is expected to match the Java/JDBC default yyyy-MM-dd hh:mm:ss.fff

[kulbhushanc]

Hi,

Is there any function available in teiid which provides timeStamp range functionality on select query. And also I am trying to fire select query on database created view-"V$LOGMNR_CONTENTS" , for this it throws exception that "Group does not exist: "V$LOGMNR_CONTENTS"".

Query is:

SELECT SQL_REDO FROM "V$LOGMNR_CONTENTS";

[shawkins]

> Is there any function available in teiid which provides timeStamp range functionality on select query.

You can still use BETWEEN, you just need to specify the timestamp values in a way that is recognized by Teiid:

TIMESTAMPCOL BETWEEN TIMESTAMP '2017-01-19 19:22:03' AND TIMESTAMP '2017-01-19 19:22:24'

> And also I am trying to fire select query on database created view-"V$LOGMNR_CONTENTS" , for this it throws exception that "Group does not exist: "V$LOGMNR_CONTENTS"".

You need to check that the table was imported for use by Teiid.  In designer you would look under the appropriate model, or with a deployed VDB you can query SYS.Tables.