loading..
Русский    English
04:24

CASE statement page 2

The CASE statement may be used in one of two syntax forms:

The first form

  1. CASE < input expression >
  2.    WHEN < WHEN expression 1>
  3.    THEN < RETURN expression 1 >
  4.    …
  5.    WHEN < WHEN expression N >
  6.    THEN < RETURN expression N >
  7. [ELSE < RETURN expression >]
  8. END

The second form

  1. CASE
  2.    WHEN < predicate 1 >
  3.    THEN < RETURN expression 1 >
  4.    …
  5.    WHEN < predicate N >
  6.    THEN < RETURN expression N >
  7. [ELSE < RETURN expression >]
  8. END

All WHEN clauses must be in the same syntax form, i.e. first and second forms cannot be mixed. When using the first syntax form, the WHEN condition is satisfied as soon as the value of when expression will be equal to the value of input expression. When using the second syntax form, the WHEN condition is satisfied as soon as the predicate evaluates to TRUE. When satisfying condition, the CASE statement returns the return expression from the corresponding THEN clause. If no WHEN expression is satisfied, the return expression from the ELSE clause will be used. If no ELSE clause is specified, a NULL value will be returned. If more than one condition are satisfied, the first return expression from them will be returned.

The above example uses the second form of the CASE statement.

It should be noted that checking for NULL could also be made using the standard function COALESCE, which is simpler. This function has arbitrary number of arguments and returns the first not-NULL expression among them. In the case of two arguments, COALESCE(A, B) is equivalent to the following CASE statement:

  1. CASE
  2. WHEN A IS NOT NULL
  3. THEN A
  4. ELSE B
  5. END

When using the COALESCE function, the solution to the above example may be rewritten as follows

Console
Execute
  1. SELECT DISTINCT Product.model,
  2. COALESCE(CAST(price AS CHAR(20)),'Not available') price
  3. FROM Product LEFT JOIN
  4. PC ON Product.model = PC.model
  5. WHERE Product.type = 'pc';

Usage of the first syntax form of the CASE statement can be demonstrated by the following example: Get all available PC models, their prices, and information about the most expensive and cheap models.

Example 5.10.1

Get all available PC models, their prices, and information about the most expensive and cheap models.

Console
Execute
  1. SELECT DISTINCT model, price,
  2. CASE price
  3. WHEN (SELECT MAX(price)
  4. FROM PC
  5. )
  6. THEN 'Most expensive'
  7. WHEN (SELECT MIN(price)
  8. FROM PC
  9. )
  10. THEN 'Most cheap'
  11. ELSE 'Mean price'
  12. END comment
  13. FROM PC
  14. ORDER BY price;

The result set

model price comment
1232 350.0 Most cheap
1260 350.0 Most cheap
1232 400.0 Mean price
1232 600.0 Mean price
1233 600.0 Mean price
1121 850.0 Mean price
1233 950.0 Mean price
1233 980.0 Most expensive

CASE operator can be used not only in SELECT clause. Here you can find other examples of its use. 

Suggested exercises: 52, 59, 64, 65, 66, 68, 69, 74, 75, 83, 84, 869192, 93, 99, 110, 111, 113, 118, 126, 128, 130, 131, 132

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100