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.