loading..
Русский    English
23:47

CASE statement page 1

Let the list of all the models of PC is required along with their prices. Besides that, if the model is not on sale (not in PC table), in the place of price must be the text "Not available".

The list of all the PC models with their prices we can obtain running the query:

Console
Execute
  1. SELECT DISTINCT Product.model, price
  2. FROM Product LEFT JOIN
  3. PC ON Product.model = PC.model
  4. WHERE product.type = 'pc';

Missing prices will be replaced by NULL values:

Model price
1121 850
1232 350
1232 400
1232 600
1233 600
1233 950
1233 980
1260 350
2111 NULL
2112 NULL

The CASE statement helps us to get required text instead of NULLs:

Console
Execute
  1. SELECT DISTINCT product.model,
  2. CASE
  3. WHEN price IS NULL
  4. THEN 'Not available'
  5. ELSE CAST(price AS CHAR(20))
  6. END price
  7. FROM Product LEFT JOIN
  8. PC ON Product.model = PC.model
  9. WHERE product.type = 'pc';

Depending on defined conditions, the CASE statement returns one of the possible values. The condition in above example is the checking for NULL. If this condition is satisfied, the text "Not available" will be returned; otherwise (ELSE) it will be the price.

One principal moment is here. As a table is always the result of the SELECT statement, all values from any column must be of the same data type (having regard to implicit type conversions). Then, we cannot combine character constant with a price (numeric type) within a single column. That is the reason why we use the type conversion to the price column to reduce its type to character. As a result, we get

model price
1121 850
1232 350
1232 400
1232 600
1233 600
1233 950
1233 980
1260 350
2111 Not available
2112 Not available

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 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 EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100