OBIEE 12c : Some Analysis may Return ORA-00937 Error After Upgrading From 11G (Doc ID 2357135.1)

Last updated on OCTOBER 12, 2022

Applies to:

Symptoms

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

• One is using both case expression and subquery
• Another is using Aggregate functions

You will see the following error.
--------------------------------------------
odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
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.

Changes

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

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

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.