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.
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 DELAYEDSimilar 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 ORDERSimple 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 StockA 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.
 | Mastering Oracle SQL |
Top
