My Oracle Support Banner

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 to [Release 1.0]
Business Intelligence Suite Enterprise Edition - Version and later
Information in this document applies to any platform.


Ran Summary Advisor utility in OBIEE 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: [[
SAWITH0 AS (select distinct T354568.Product_Cl0005582C as c1,
     T354568.Product_In0005582E as c2
     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
     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 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:


To view full details, 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 a vibrant support community of peers and Oracle experts.