OBIEE 12c : Some Analysis may Return ORA-00937 Error After Upgrading From 11G

(Doc ID 2357135.1)

Last updated on MARCH 05, 2018

Applies to:

Business Intelligence Suite Enterprise Edition - Version to [Release 11g to 12g]
Information in this document applies to any platform.


In BIEE 12c, when opening an analysis which has the following columns, ORA-00937 error happens.

You will see the following error.
odbc driver returned an error (SQLExecDirectW).
Error Details
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 937, message: ORA-00937: not a single-group group function at OCI call OCIStmtExecute. (HY000)
State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)
SQL Issued: SELECT 0 s_0, case when "SCOTT"."EMP"."DEPTNO" in (SELECT "SCOTT"."EMP"."DEPTNO" FROM "SCOTT" WHERE "EMP"."ENAME" like 'M%') then 'DEPT including Person M' else "SCOTT"."EMP"."JOB" end s_1, SUM("SCOTT"."EMP"."SAL") s_2 FROM "SCOTT" FETCH FIRST 65001 ROWS ONLY

Test case to reproduce
The following is the simple test case using EMP, DEPT tables of SCOOT schema.

1. Using Admin tool, import EMP, DEPT tables (of scott user)to the physical layer of a repository.

2. Drag and Drop physical schema(created in step.1) to the Business Model and Mapping layer.

3. Drag and Drop it to the presentation layer

4. Save the repository.

5. Login to BIEE(http:server:port/anaytics).

6. Create new analysis using the subject area created in above step.
 6-1. Select "EMP"."DEPTMO" and "EMP"."SAL".
 6-2. Edit Formula for "EMP"."DEPTNO" as follows.
       Column Heading: TestColumn
       Column Formula: case when "EMP"."DEPTNO" in (SELECT "EMP"."DEPTNO" FROM "SCOTT" WHERE "EMP"."ENAME" like 'M%') then 'DEPT including Person M' else "EMP"."JOB" end
 6-3. Edit Formula for "EMP"."SAL" as follows.
       Column Formula: SUM("EMP"."SAL")

7. Open 'Results tab' and you will see the error above.



After upgrading to BIEE 12c (or BIEE 11g from BIEE 11g, ORA-00937 occurs on some analysis.

This analysis did not have ORA-00937 error on BIEE 11g
But when this analysis is opened on BIEE 12c (or BIEE 11g, ORA-00937 error occurs.


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