Expression Builder wizard - CASE Options page

Use this page to select the type of CASE expression that you want to create.

A CASE expression can be either a searched or a simple type.

Searched-WHEN-Clause
A sequence of possible search conditions are evaluated to determine a result. A searched CASE expression consists of the CASE keyword and one or more WHEN clauses. For example:
CASE
   WHEN CREDIT_LIMIT > 2999 THEN 'A'
   WHEN CREDIT_LIMIT > 1999 THEN 'B'
   WHEN CREDIT_LIMIT >  999 THEN 'C'
   WHEN CREDIT_LIMIT >  499 THEN 'D'   
This type does not have an expression immediately after the CASE keyword. Each WHEN clause contains a search condition that is evaluated in the order in which it appears after the keyword.

In the example above, a credit limit value that is 3000 or higher is assigned the rating 'A,' a credit limit value that is between 2000 and 2999 is assigned the rating 'B,' and so on.

Simple-WHEN-Clause
A single condition is evaluated to determine a result. A simple CASE expression consists of the CASE keyword, a value expression, and one or more WHEN clauses. For example:
CASE CREDIT_LIMIT
         WHEN 3000 THEN 'A'
         WHEN 2000 THEN 'B'
         WHEN 1000 THEN 'C'
         WHEN  500 THEN 'D'
The expression after the keyword (CREDIT_LIMIT in the example) is compared to each WHEN clause in the order that they appear until a match is found between the value of the expression and the WHEN clause. The limitation of a simple CASE statement is that the WHEN clause must contain a value expression; it cannot contain a search condition.

In the example above, a credit limit value of 3000 is assigned the rating 'A,' a credit limit value of 2000 is assigned the rating 'B,' and so on. There is no provision for assigning a rating to credit limit values that are not specified in the WHEN clauses.

Related tasks
Creating expressions in the Expression Builder wizard
Editing expressions in the Expression Builder wizard
Replacing expressions in the Expression Builder wizard
Related reference
Expression Builder wizard - Expression Types page

Feedback