OBIEE 11g: Report SQL Different after Applying 11.1.1.7.140527 Bundle Patch (Doc ID 1910297.1)

Last updated on AUGUST 07, 2014

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.7.140225 and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.140225 and later
Information in this document applies to any platform.

Symptoms

You have a report in OBIEE 11.1.1.7.0 which uses the  FILTER function along with the condition for the filter. OBIEE puts condition of the filter into case statement in SELECT clause
For example:

WITH
SAWITH0 AS (select /*+ LEADING(T153070) */ sum(case when T21974.BUSINESS_DT = TO_DATE('2014-02-03','YYYY-MM-DD') then T21974.DTD_POSTED_TRAN_AMT end ) as c1,
...
...
where ( T21974.BUSINESS_DT = T153070.REPORT_DT and T21974.FISCAL_YR = T153070.FISCAL_YR and T21974.LEGAL_ENTITY_CD = 'NSC' and T153070.BUSINESS_DT = TO_DATE('2014-02-03 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and T153070.ACCT_PRD between T21974.ACCT_PRD and T21974.ACCT_PRD_END )

After applying the 11.1.1.7.140527 bundle patch, OBIEE changes the SQL generated for the report.  The condition of the filter is put into WHERE clause.
For example:

WITH
SAWITH0 AS (select /*+ LEADING(T153070) */ sum(T21974.DTD_POSTED_TRAN_AMT) as c1,
...
...
where ( T21974.BUSINESS_DT = T153070.REPORT_DT and T21974.BUSINESS_DT = TO_DATE('2014-02-03','YYYY-MM-DD') and T21974.FISCAL_YR = T153070.FISCAL_YR and T21974.LEGAL_ENTITY_CD = 'NSC' and T153070.BUSINESS_DT = TO_DATE('2014-02-03 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and T153070.ACCT_PRD between T21974.ACCT_PRD and T21974.ACCT_PRD_END )
group by T21974.BUSINESS_DT)

This affects the result of the report.

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