A CASE expression can be either a searched or a simple type.
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.
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.