Team LiB   Previous Section   Next Section

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 8.1.6 release. Like the DECODE function, the CASE expression enables conditional logic within an 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. While 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, 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 Oracle9i, 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, the "C"s represent conditions, and the "R"s 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 the desired results are achieved. The next 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;

Similar to DECODE, all results in the CASE expression must have comparable types; otherwise, ORA-932 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
FROM cust_order co
WHERE co.ship_dt IS NULL AND co.cancelled_dt IS NULL;

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

Therefore, 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. As a result, 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, supplier s
WHERE p.supplier_id = s.supplier_id;

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, simple expressions may prove more efficient if the expression being evaluated is computed via a function call.

    Team LiB   Previous Section   Next Section