Using the CASE Expression


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

Using Simple CASE Expressions

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 

Using Searched CASE Expressions

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 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net