The syntax for the case statement is as follows:
CASE WHEN condition THEN action [WHEN ...] [ELSE otheraction] END
The CASE statement is similar to the if/then/else statements in other languages. The condition specified returns a boolean result. If the evaluated result is true, then it performs the action . If false, then it can performs an else . When a result is not provided, it returns NULL.
Using the publish table, we can specify it to return suggested prices where if under 10, then it is low priced. If the suggested price is between $11 and $15, then it is moderately priced. Anything over that range is high priced. The statement in Example 4-27 returns the prices and publisher names:
Example 4-27. Using Case Statements
SELECT publ_name, sugg_price, CASE WHEN sugg_price <='10' THEN 'low priced' WHEN sugg_price <='15' THEN 'moderately priced' ELSE 'expensive' END FROM publish ORDER BY publ_name;
The result is:
REWORK THIS EXAMPLE ENTIRELY. :) (6 rows)