Oracle Exalytics OBIEE 11g Generated Query on TimesTen Data Source Fails With Parse Error: TT7025 Illegal use of reserved keyword

(Doc ID 1560128.1)

Last updated on JANUARY 14, 2016

Applies to:

Oracle Exalytics Software - Version 1.0.0.2.0 and later
Oracle TimesTen In-Memory Database - Version 11.2.2.4.1 to 11.2.2.8.0 [Release 11.2]
Business Intelligence Reporting and Publishing - Version 11.1.1.6.1 and later
Information in this document applies to any platform.
Checked for relevance 14-JAN-2016.

Symptoms

Certain queries in Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.6+ and OBIEE 11.1.1.7+ against TimesTen 11.2.2.+ data sources on an Oracle Exalytics server, fail to execute successfully.
The queries that fail include SQL with CASE statements in the SELECT and WHERE clauses.

For example:

WITH
SAWITH0 AS (select sum(case when T361921.QTR_DESC in ('CY 2011 Q2') then T377669.SHIP_AMT_RPT end ) as c1,
  sum(case when T361921.QTR_DESC in (case when substr('CY 2011 Q2' , 9 , 2) = 'Q1' then substr('CY 2011 Q2' , 1 , 3) || TO_CHAR( TO_NUMBER( substr('CY 2011 Q2' , 4 , 4) ) - 1 ) || ' Q4' when substr('CY 2011 Q2' , 9 , 2) = 'Q2' then substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q1' when substr('CY 2011 Q2' , 9 , 2) = 'Q3' then substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q2' else substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q3' end ) then T377669.SHIP_AMT_RPT end ) as c2,
  TO_NUMBER( T366446.WK_NBR ) as c3,
  T366739.MFG_BU_NAME as c4,
  T366739.MFG_BU_CD as c5
from
  DDR_R_BASE_DAY_DN_MV T366446,
  DDR_TIME_CLNDR_DAY_V T361921,
...
  ATVI_DDR_B_RTL_SHIP_ITEM_DAY T377669
where ( T361921.DAY_CD = T377669.DAY_CD and T366446.DAY_CD = T377669.DAY_CD and T366446.MFG_ORG_CD = T366446.ORG_CD and T366739.MFG_SKU_ITEM_ID = T377669.MFG_SKU_ITEM_ID and T366739.MFG_BU_CD = T376892.MFG_BU_CD and T376892.USER_NAME = upper('weblogic') and T376895.REGION_DESC = T377583.LEVEL4_DESC and T376895.USER_NAME = upper('weblogic') and T377583.ORG_BSNS_UNIT_ID = T377669.ORG_BSNS_UNIT_ID and TO_NUMBER( T366446.WK_NBR ) = 201252 and (T361921.QTR_DESC = case when substr('CY 2011 Q2' , 9 , 2) = 'Q1' then substr('CY 2011 Q2' , 1 , 3) || TO_CHAR( TO_NUMBER( substr('CY 2011 Q2' , 4 , 4) ) - 1 ) || ' Q4' when substr('CY 2011 Q2' , 9 , 2) = 'Q2' then substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q1' when substr('CY 2011 Q2' , 9 , 2) = 'Q3' then substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q2' else substr('CY 2011 Q2' , 1 , 3) || substr('CY 2011 Q2' , 4 , 5) || 'Q3' end or T361921.QTR_DESC = 'CY 2011 Q2') )
...

 

The error message reported by OBIEE when running the query is similar to:

Query Status: [nQSError: 16002] Cannot obtain number of columns for the query result. [[^M
[nQSError: 16001] ODBC error state: 37000 code: 7025 message:
[TimesTen][TimesTen 11.2.2.0.0 ODBC Driver][TimesTen]TT7025: Illegal use of reserved keyword "ORDER", character position: 476 -- file "ptSqlY.y", lineno
9091, procedure "reserved_word_or_syntax_error".
[nQSError: 43119] Query Failed:


When running the query directly against TimesTen (without OBIEE front end), the error is similar to:

..parse error...TT7025 Illegal use of reserved keyword

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms