Mastering Oracle SQL

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.^[14]

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

Searched CASE Expressions

A searched CASE expression evaluates a number of conditions and returns a result determined by which condition ...