| 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. | 