Aggregate Script Generated By Exalytics OBIEE Summary Advisor Fails With Error: "TT1001: Syntax error in SQL statement before or at: "INSERT, Reserved_word_or_syntax_error" (Doc ID 1473184.1)

Last updated on JULY 19, 2012

Applies to:

Oracle Exalytics Software - Version 1.0.0.0.0 to 1.0.0.0.0 [Release 1.0]
Business Intelligence Suite Enterprise Edition - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

Ran Summary Advisor utility in OBIEE 11.1.1.6 Admin Tool to to create aggregates on TimesTen on an Exalytics server.  The summary advisor completes successfully and an aggregate creation script is generated.
When trying to run the create aggregates script via the nqcmd utility, it fails when issuing the following query:

[OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: 004krQ4Q4bA3V8YFLrBT8A0000SH003DAe] [tid: 44641940] [requestid: 10b00011] [sessionid: 10b00000] [username: biadmin] -------------------- Sending query to database named tt_newco (id: <<1265620>>), connection pool named tt_newco, logical request hash 837a29c8, physical request hash fc0ac8d9: [[
WITH
SAWITH0 AS (select distinct T354568.Product_Cl0005582C as c1,
     T354568.Product_In0005582E as c2
from
     SA_Product00055996 T354568)
INSERT INTO SA_Product00055997 ( Product_Cl0005582C, Product_In0005582E, Product_00055997SK ) select distinct D1.c1 as c1,
     D1.c2 as c2,
     ROW_NUMBER() OVER ( ORDER BY D1.c1, D1.c2) as c3
from
     SAWITH0 D1
]]

The error message in the nqquery.log is:

[OracleBIServerComponent] [TRACE:3] [USER-34] [] [ecid: 004krQ4Q4bA3V8YFLrBT8A0000SH003DAe] [tid: 44641940] [requestid: 10b00011] [sessionid: 10b00000] [username: biadmin] -------------------- Query Status: [nQSError: 16002] Cannot obtain number of columns for the query result. [[
[nQSError: 16001] ODBC error state: 37000 code: 1001 message: [TimesTen][TimesTen 11.2.2.2.0 ODBC Driver][TimesTen]TT1001: Syntax error in SQL statement before or at: "INSERT", character position: 146 -- file "ptSqlY.y", lineno 9127, procedure "reserved_word_or_syntax_error".
[nQSError: 43119] Query Failed:
]]

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