My Oracle Support Banner

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

Last updated on NOVEMBER 22, 2019

Applies to:

Oracle OLAP - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

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

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.