Case

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

Syntax

The simple 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.

CASE 
case_operand
simple_when_clause...
[ else_clause ]
END

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.

CASE
searched_when_clause...
[ else_clause ]
END

Parameter	Description
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, a string value expression, or a set.
else_clause	Optional. Syntax: ELSE numeric_value_expression \| set \| string_value_expression
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, a string value expression, or a set.

Example

Example for Simple Case Expression

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:

(axis)	ProductOunces
Product	0
Colas	0
Cola	12
Diet Cola	12
Caffeine Free Cola	16
Root Beer	0
Old Fashioned	12
Diet Root Beer	16
Sarsaparilla	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

Example for Searched Case Expression

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:

(axis)	ProfitCategory
Product	4
Colas	4
Cola	4
Diet Cola	3
Caffeine Free Cola	1
Root Beer	4
Old Fashioned	3
Diet Root Beer	4
Sarsaparilla	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