You use the CASE expression to perform if-then-else logic in SQL without having to use PL/SQL. CASE works in a similar manner to DECODE() , but you should use CASE since it is ANSI-compliant.
There are two types of CASE expressions:
Simple case expressions, which use expressions to determine the returned value
Searched case expressions, which use conditions to determine the returned value
Simple CASE expressions use expressions to determine the returned value and have the following syntax:
CASE search_expression WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN ELSE default_result END
where
search_expression is the expression to be evaluated.
expression1, expression2, ..., expressionN are the expressions to be evaluated against search_expression .
result1, result2, ..., resultN are the returned results (one for each possible expression). If expression1 evaluates to search_expression , result1 is returned, and so on.
default_result is the default result returned when no matching expression is found.
The following example illustrates the use of a simple CASE expression:
SELECT product_id, product_type_id, CASE product_type_id WHEN 1 THEN 'Book' WHEN 2 THEN 'Video' WHEN 3 THEN 'DVD' WHEN 4 THEN 'CD' ELSE 'Magazine' END FROM products; PRODUCT_ID PRODUCT_TYPE_ID CASEPROD ---------- --------------- -------- 1 1 Book 2 1 Book 3 2 Video 4 2 Video 5 2 Video 6 2 Video 7 3 DVD 8 3 DVD 9 4 CD 10 4 CD 11 4 CD 12 Magazine
Searched CASE expressions use conditions to determine the returned value and have the following syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE default_result END
where
condition1, condition2, ..., conditionN are the expressions to be evaluated.
result1, result2, ..., resultN are the returned results (one for each possible condition). If condition1 is true, result1 is returned, and so on.
default_result is the default result returned when no true condition is found.
The following example illustrates the use of a searched CASE expression:
SELECT product_id, product_type_id, CASE WHEN product_type_id = 1 THEN 'Book' WHEN product_type_id = 2 THEN 'Video' WHEN product_type_id = 3 THEN 'DVD' WHEN product_type_id = 4 THEN 'CD' ELSE 'Magazine' END FROM products; PRODUCT_ID PRODUCT_TYPE_ID CASEPROD ---------- --------------- -------- 1 1 Book 2 1 Book 3 2 Video 4 2 Video 5 2 Video 6 2 Video 7 3 DVD 8 3 DVD 9 4 CD 10 4 CD 11 4 CD 12 Magazine
You can use operators in a searched CASE expression, as shown in the following example:
SELECT product_id, price, CASE WHEN price > 15 THEN 'Expensive' ELSE 'Cheap' END FROM products; PRODUCT_ID PRICE CASEWHENP ---------- ---------- --------- 1 19.95 Expensive 2 30 Expensive 3 25.99 Expensive 4 13.95 Cheap 5 49.99 Expensive 6 14.95 Cheap 7 13.49 Cheap 8 12.99 Cheap 9 10.99 Cheap 10 15.99 Expensive 11 14.99 Cheap 12 13.49 Cheap