Database Tutorial

Week #7: Articles

Learning Outcomes

• Learn how to use the CASE operator.

CASE Operator

Originally, the SQL language did not include an if-statement because one was thought unnecessary. Oracle added a DECODE statement that performed an if-statement behavior. Oracle’s DECODE statement is difficult to use and limited to only value comparisons. Other vendors implemented their proprietary solutions but each was limited to certain behaviors.

The ANSI SQL language added a CASE operator that would support the following if-statement logic:

• String and math value comparisons
• Date value comparisons
• Range comparisons against a list of literal values
• Range comparisons against a result set from a query

ANSI SQL CASE Operator

The CASE operator is the most portable operator, and it allows for equality and inequality evaluation, range comparisons, and in-set comparisons. It also supports multiple CASE statements, such as a switch statement without fall-through characteristics. You can likewise use comparisons against subqueries and correlated subqueries.

In an Oracle database, the following query matches case-insensitive strings from the inline view against string literals for the primary colors on the color wheel:

SQL> SELECT   inline.color_name
  2  ,        CASE
  3             WHEN UPPER(inline.color_name) = 'BLUE' THEN
  4               'Primary Color'
  5             WHEN UPPER(inline.color_name) = 'RED' THEN
  6               'Primary Color'
  7             WHEN UPPER(inline.color_name) = 'YELLOW' THEN
  8               'Primary Color'
  9             ELSE
 10               'Not Primary Color'
 11           END AS color_type
 12  FROM    (SELECT 'Red' AS color_name FROM dual
 13           UNION ALL
 14           SELECT 'Blue' AS color_name FROM dual
 15           UNION ALL
 16           SELECT 'Purple' AS color_name FROM dual
 17           UNION ALL
 18           SELECT 'Green' AS color_name FROM dual
 19           UNION ALL
 20           SELECT 'Yellow' AS color_name FROM dual) inline
 21  ORDER BY 2 DESC, 1 ASC

The CASE operator includes several WHEN clauses that evaluate conditions and an ELSE clause that acts as the default catchall for the CASE operator. Note that END by itself terminates a CASE operator. If you were to put END CASE, the word CASE would become the column alias.

Although the sample evaluates only a single logical condition, each WHEN clause supports any number of AND or OR logical operators. Any comparison phrase can use the standard equality and inequality comparison operators; the IN, =ANY, =SOME, and =ALL lookup operators; and scalar, single-row, multiple-row, and correlated subqueries.
You would get the following results from the preceding query in Oracle—at least you would when you format the color_name column to an alphanumeric ten-character string in SQL*Plus (check Appendix A for syntax):

COLOR_NAME COLOR_TYPE
---------- -----------------
Blue       Primary Color
Red        Primary Color
Yellow     Primary Color
Green      Not Primary Color
Purple     Not Primary Color

There’s a lot of power in using the CASE operator, but you need to understand the basics and experiment. For example, you can write a query with a CASE operator that returns whether or not an item is found in inventory, like this:

SELECT CASE
         WHEN 'Star Wars VII' IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM   dual;

The CASE operator also allows you to validate complex math or date math.