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.


Feedback