9.2 The Case for CASE

The CASE expression made its SQL debut in the SQL-92 specification in 1992. Eight years later, Oracle included the CASE expression in the Oracle8i release. Like the DECODE function, the CASE expression enables conditional logic within a SQL statement, which might explain why Oracle took so much time implementing this particular feature. If you have been using Oracle for a number of years, you might wonder why you should care about the CASE expression, since DECODE does the job nicely. Here are several reasons why you should make the switch:

  • CASE expressions can be used everywhere that DECODE functions are permitted.

  • CASE expressions are more readable than DECODE expressions.

  • CASE expressions execute faster than DECODE expressions.[1]

    [1] Since CASE is built into Oracle's SQL grammar, there is no need to call a function in order to evaluate the if-then-else logic. Although the difference in execution time is miniscule for a single call, the aggregate time savings from not calling a function should become noticeable when working with large result sets.

  • CASE expressions handle complex logic more gracefully than DECODE expressions.

  • CASE is ANSI-compliant, whereas DECODE is proprietary.

The only downside to using CASE over DECODE is that CASE expressions are not supported in Oracle8i's PL/SQL language. If you are using Oracle9i Database or Oracle Database 10g, however, any SQL statements executed from PL/SQL may include CASE expressions.

The SQL-92 specification defines two distinct flavors of the CASE expression: searched and simple. Searched CASE expressions are the only type supported in the Oracle8i release. If you are using a later release, you may also use simple CASE expressions.

9.2.1 Searched CASE Expressions

A searched CASE expression evaluates a number of conditions and returns a result determined by which condition is true. The syntax for the searched CASE expression is as follows:

CASE

  WHEN C1 THEN R1

  WHEN C2 THEN R2

   . . . 

  WHEN CN THEN RN

  ELSE RD

END

In the syntax definition, C1, C2 . . . Cn represent conditions, and R1, R2 . . . RN represent results. You can use up to 127 WHEN clauses in each CASE expression, so the logic can be quite robust. Conditions are evaluated in order. When a condition is found that evaluates to TRUE, the corresponding result is returned, and execution of the CASE logic ends. Therefore, carefully order WHEN clauses to ensure that your desired results are achieved. The following example illustrates the use of the CASE statement by determining the proper string to show on an order status report:

SELECT co.order_nbr, co.cust_nbr,

  CASE WHEN co.expected_ship_dt IS NULL THEN 'NOT YET SCHEDULED'

    WHEN co.expected_ship_dt <= SYSDATE THEN 'SHIPPING DELAYED'

    WHEN co.expected_ship_dt <= SYSDATE + 2 THEN 'SHIPPING SOON'

    ELSE 'BACKORDERED' 

  END ship_status

FROM cust_order co

WHERE co.ship_dt IS NULL AND co.cancelled_dt IS NULL;



ORDER_NBR   CUST_NBR SHIP_STATUS

---------- ---------- -----------------

      1001          1 SHIPPING DELAYED

      1003          4 SHIPPING DELAYED

      1004          4 SHIPPING DELAYED

      1005          8 SHIPPING DELAYED

      1007          5 SHIPPING DELAYED

      1008          5 SHIPPING DELAYED

      1009          1 SHIPPING DELAYED

      1012          1 SHIPPING DELAYED

      1017          4 SHIPPING DELAYED

      1019          4 SHIPPING DELAYED

      1021          8 SHIPPING DELAYED

      1025          5 SHIPPING DELAYED

      1027          5 SHIPPING DELAYED

      1029          1 SHIPPING DELAYED

Similar to DECODE, all results in a CASE expression must have comparable types; otherwise, ORA-00932 will be thrown. Each condition in each WHEN clause is independent of the others, however, so your conditions can include various data types, as demonstrated in the next example:

SELECT co.order_nbr, co.cust_nbr,

  CASE

    WHEN co.sale_price > 10000 THEN 'BIG ORDER'

    WHEN co.cust_nbr IN 

     (SELECT cust_nbr FROM customer WHERE tot_orders > 100) 

      THEN 'ORDER FROM FREQUENT CUSTOMER'

    WHEN co.order_dt < TRUNC(SYSDATE) -- 7 THEN 'OLD ORDER'

    ELSE 'UNINTERESTING ORDER'

  END order_type

FROM cust_order co

WHERE co.ship_dt IS NULL AND co.cancelled_dt IS NULL;



ORDER_NBR   CUST_NBR ORDER_TYPE

---------- ---------- ------------

      1001          1 OLD ORDER

      1003          4 OLD ORDER

      1004          4 OLD ORDER

      1005          8 OLD ORDER

      1007          5 OLD ORDER

      1008          5 OLD ORDER

      1009          1 OLD ORDER

      1012          1 OLD ORDER

      1017          4 OLD ORDER

      1019          4 OLD ORDER

      1021          8 OLD ORDER

      1025          5 OLD ORDER

      1027          5 OLD ORDER

      1029          1 OLD ORDER

9.2.2 Simple CASE Expressions

Simple CASE expressions are structured differently than searched CASE expressions in that the WHEN clauses contain expressions instead of conditions, and a single expression to be compared to the expressions in each WHEN clause is placed in the CASE clause. Here's the syntax:

CASE E0

  WHEN E1 THEN R1

  WHEN E2 THEN R2

   . . . 

  WHEN EN THEN RN

  ELSE RD

END

Each of the expressions E1...EN are compared to expression E0. If a match is found, the corresponding result is returned; otherwise, the default result (RD) is returned. All of the expressions must be of the same type, since they all must be compared to E0, making simple CASE expressions less flexible than searched CASE expressions. The next example illustrates the use of a simple CASE expression to translate the status code stored in the part table:

SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,

  CASE p.status

    WHEN 'INSTOCK' THEN 'In Stock'

    WHEN 'DISC' THEN 'Discontinued'

    WHEN 'BACKORD' THEN 'Backordered'

    WHEN 'ENROUTE' THEN 'Arriving Shortly'

    WHEN 'UNAVAIL' THEN 'No Shipment Scheduled'

    ELSE 'Unknown'

  END part_status

FROM part p INNER JOIN supplier s

ON p.supplier_id = s.supplier_id;



PART_NBR         PART_NAME               SUPPLIER            PART_STATUS

---------------- ----------------------- ------------------- ------------

AI5-4557         Acme Part AI5-4557      Acme Industries     In Stock

TZ50828          Tilton Part TZ50828     Tilton Enterprises  In Stock

EI-T5-001        Eastern Part EI-T5-001  Eastern Importers   In Stock

A searched CASE can do everything that a simple CASE can do, which is probably the reason Oracle only implemented searched CASE expressions the first time around. For certain uses, such as translating values for a column, a simple expression may prove more efficient if the expression being evaluated is computed via a function call.