Help with Case statement

[dbomrrsm]

I have a universe measure with the follwoing code in the select

CASE
WHEN CMR_DPP.PRODUCT_DIM.DIVISION = 1 AND CMR_DPP.BRAND_DIM.COMPANY = 'LW'
THEN @Select(Weekly Base Measures\ABORTED_DEMAND_VAL) * 4
WHEN CMR_DPP.PRODUCT_DIM.DIVISION != 1 AND CMR_DPP.BRAND_DIM.COMPANY = 'LW'
THEN @Select(Weekly Base Measures\ABORTED_DEMAND_VAL) * 2
ELSE @Select(Weekly Base Measures\ABORTED_DEMAND_VAL)
END

When I parse I get a ORA 00937 error not a single group function.

I think the problem is that @select(Weekly Base Measures\DEMAND_VAL) is an aggregate aware measure.

If I try to build this case statement into the base measure that is aggregate aware I get an error about multiple calls to aggregate awareness.

Can anyone help me with this - maybe I am just doing something really stupid.

TIA

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[skrandel]

You're using @Select. But, the object that you are @selecting has a SUM on it.

If you look at the actual SQL, it doesn't work.

Sum(table.ABORTED_DEMAND_VAL) * 4 is NOT valid SQL for a Select clause.

Get rid of the @Select and use the actual table.column and it will work.

Steve Krandel
Symantec

 

[dbomrrsm]

Steve

Thanks for the info - I actually managed to sort this one yesterday.

The problem was as you say - the @Select was a select of a base measure that had aggregate awareness on it and summed values - I had to build the case into the aggregate awareness and it works fine.

Thanks for your time.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]