problem or bug in oracle with Cube operator?

Does anyone have much experience using the new olap functionality in oracle 8i?

I'm trying to make use of the cube and grouping operators but am running into a problem...which I can't help but think is a bug in Oracle.

The problem exists in 8.1.5 as well as in 8.1.6

To sum up the problem:

If a query is sent that constrains a column to a single value, the cube/grouping functions seem to produce somewhat erroneous results.
Note: It only happens when constraining on a single value.

For example:

SELECT SUM ( A.SALES ),
DECODE(GROUPING(B.PRODUCT),1,'**TOTAL**', B.PRODUCT) as COL1 , DECODE(GROUPING(B.COLOR),1,'**TOTAL**', B.COLOR) as COL2 FROM FACT_TABLE A, PRODUCT B WHERE (A.PROD_ID=B.PROD_ID) and B.COLOR IN ('RED') GROUP BY cube(B.PRODUCT,B.COLOR)

NOTE: issuing B.COLOR ='RED' has the same problem so it is not related to the IN operator

Yields:

SUM(A.SALES) COL1 COL2

----------------- ----------  -------
              307 WIDGETS     RED
              307             **TOTAL**
              307 **TOTAL**
              307 **TOTAL**   **TOTAL**

What makes this result set erroneous are the nulls that appear in the 2nd and 3rd rows of COL1 and COL2.

Now, if I alter the statement to add a competely bogus value which I know doesn't exist in the column as follows, then it seems to work
properly:

SELECT SUM ( A.SALES ),
DECODE(GROUPING(B.PRODUCT),1,'**TOTAL**', B.PRODUCT) as COL1 , DECODE(GROUPING(B.COLOR),1,'**TOTAL**', B.COLOR) as COL2 FROM FACT_TABLE A, PRODUCT B WHERE (A.PROD_ID=B.PROD_ID) and B.COLOR IN ('RED','BOGUS-COLOR-VALUE') GROUP BY cube (B.PRODUCT,B.COLOR) SUM(A.SALES) COL1 COL2

----------------- ----------  --------
	      307 WIDGETS     RED
              307 WIDGETS     **TOTAL**
              307 **TOTAL**   RED
              307 **TOTAL**   **TOTAL**

Has anyone run into this problem? Is it a bug in Oracle? If it's not a bug, I'd appreciate if someone could explain to me the logic for
why it's not so that I can have a better understanding of how cube and grouping should work.

Thanks,
Gavin

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!