Case

The CASE keyword begins a conditional expression. There are two types of conditional test you can perform using CASE:

Simple Case Expression
Searched Case Expression

Simple Case Expression

The case expression evaluates case_operand and returns a result based on its value, as specified by WHEN or ELSE clauses. The result of a case expression can be a value expression or a set.

If no ELSE clause is specified, and none of the WHEN clauses is matched, an empty value/empty set is returned.

Syntax

CASE 
case_operand
simple_when_clause...
[ else_clause ]
END
case_operand An expression to evaluate.
simple_when_clause One or more WHEN/THEN statements. Syntax:
WHEN when_operand THEN result
when_operand A value expression.
result A numeric value expression or a set.
else_clause Optional. Syntax:
ELSE numeric_value_expression | set

Example

In the following query, the calculated member [Measures].[ProductOunces] is evaluated based on the value of the Ounce attribute for the current member of the Product dimension.

WITH MEMBER [Measures].[ProductOunces] AS
'Case Product.CurrentMember.Ounces
         when 32 then 32
         when 20 then 20
         when 16 then 16
         when 12 then 12
         else 0
end'
SELECT
{  [Measures].[ProductOunces] } ON COLUMNS,
{ [Product].Members } ON ROWS
FROM Sample.Basic

This query returns the following result:

ProductOunces
Product 0
Colas 0
Cola 12
Diet Cola 12
Caffeine Free Cola 16
Root Beer 0
Old Fashioned 12
Diet Root Beer 16
Sasparilla 12
Birch Beer 16
Cream Soda 0
Dark Cream 20
Vanilla Cream 20
Diet Cream 12
Fruit Soda 0
Grape 32
Orange 32
Strawberry 32
Diet Drinks 0
Diet Cola 0
Diet Root Beer 0
Diet Cream 0

Searched Case Expression

In searched case expression, each WHEN clause specifies a search condition and a result to be returned if that search condition is satisfied.

The WHEN clauses are evaluated in the order specified. The result is returned from the first WHEN clause in which the search condition evaluates to TRUE.

The result can be a value expression or a set.

If no ELSE clause is specified, and none of the search conditions in the WHEN clauses evaluate to TRUE, an empty value/empty set is returned.

Syntax

CASE
searched_when_clause...
[ else_clause ]
END
searched_when_clause One or more WHEN/THEN statements. Syntax:
WHEN search_condition THEN result
search_condition A value expression.
result A numeric value expression or a set.
else_clause Optional. Syntax:
ELSE numeric_value_expression | set

Example

The following query divides products into different profit categories based on Profit, and returns categories for each product.

WITH MEMBER [Measures].[ProfitCategory] AS
' Case 
      when Profit > 10000 then 4 
      when Profit > 5000  then 3 
      when Profit > 3000 then  2 
      else 1
end'
SELECT
{  [Measures].[ProfitCategory] } ON COLUMNS,
{ [Product].Members } ON ROWS
FROM Sample.Basic

This query returns the following result:

ProfitCategory
Product 4
Colas 4
Cola 4
Diet Cola 3
Caffeine Free Cola 1
Root Beer 4
Old Fashioned 3
Diet Root Beer 4
Sasparilla 2
Birch Beer 2
Cream Soda 4
Dark Cream 4
Vanilla Cream 1
Diet Cream 4
Fruit Soda 4
Grape 4
Orange 3
Strawberry 1
Diet Drinks 4
Diet Cola 3
Diet Root Beer 4
Diet Cream 4

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com