Incorrect Number of Rows Returned in a Query against an OLAP Cube with an Outer Join (Doc ID 1346143.1)

Last updated on MAY 12, 2017

Applies to:

Oracle OLAP - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

You have a query with an Outer Join and a 'WHERE' condition against an Oracle OLAP Cube .
When the query is executed, not enough rows are returned.
The following is using a measure called MOSTLYNULL in the two dimensional price_cost_cube
of the OLAPTRAIN sample schema.
The query without the where clould look like this and works fine:

SELECT C.MOSTLYNULL, P.DIM_KEY
FROM
  PRICE_COST_CUBE_VIEW C
  RIGHT OUTER JOIN
  PRODUCT_VIEW P ON (C.PRODUCT = P.DIM_KEY AND C.TIME = '1')
/
However, the following query does not return the correct results:

SELECT C.MOSTLYNULL, P.DIM_KEY
FROM
  PRICE_COST_CUBE_VIEW C
  RIGHT OUTER JOIN
  PRODUCT_VIEW P ON (C.PRODUCT = P.DIM_KEY)
WHERE C.TIME = '1'
/

Changes

The only change is the addition of the 'WHERE' condition outside the join.

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