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
|
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
|
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 |