MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Searched Case Expression

In the simple case expression example, a measure called PerfRating exists, which contains a numerical rating from 1 to 5 for each salesperson. Assume that this rating is based on the percentage by which each sales rep surpassed her budgeted sales. Anything above 100% is a performance rating of 5; from 51% to 100% is 4; from 1% to 50% is 3; from -24% to 0% is 2; and -25% or less is 1.

The simple CASE statement is not capable of expressing this logic because the WHEN operand of the simple WHEN clause is a value expression rather than a search condition. In the searched case, however, the WHEN operand is a search condition. Clearly, a CASE operand is no longer needed. The construct of the searched case clause is as follows:

<searched_case> ::= CASE
                    <searched_when_clause>...
                    [ <else_clause> ]
                    END

<searched_when_clause> ::= WHEN <search_condition> THEN <result>

The expansion of other nonterminals is the same as that for the simple case. The following example uses searched case to generate a performance rating for each sales rep, based on the logic explained above:

WITH MEMBER [Measures].[PerfTemp] AS 
   ([Measures].[Sales] - [Measures].[BudgetedSales])/
   [Measures].[BudgetedSales]*100
MEMBER [Measures].[PerfRating] AS 
   CASE
      WHEN [Measures].[PerfTemp] > 100 THEN 5
      WHEN [Measures].[PerfTemp] > 50  THEN 4
      WHEN [Measures].[PerfTemp] > 0   THEN 3
      WHEN [Measures].[PerfTemp] > -25 THEN 2
      ELSE 1
   END
SELECT {[Measures].[Sales], [Measures].[PerfRating]}
   ON COLUMNS,
   SalesPerson.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1997], [Geography].[All], [Products].[All])

Note that the WHEN clauses are evaluated in the order specified; that is, the value of CASE is the result of the first searched WHEN clause whose search condition is TRUE. That happens when the second WHEN clause in the above example checks only to see whether PerfTemp > 50 rather than whether PerfTemp > 50 AND PerfTemp <= 100.

As with the simple case, if no ELSE is specified and the search conditions of all the WHEN clauses are FALSE, the result of the CASE is an empty cell value. For more information, see "Empty Cells" in this chapter.

Finally, the simple case can be rewritten as a searched case. The simple case

CASE case_operand
   WHEN when_operand1 THEN result1
   WHEN when_operand2 THEN result2
   ...
END

is equivalent to the following searched case:

CASE
   WHEN case_operand = when_operand1 THEN result1
   WHEN case_operand = when_operand2 THEN result2
   ...
END