O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MAKING THE CASE

Conditional execution is not easy in SQL, and some implementations recommend using a custom function to format a conditional output of a query. Some rudimentary capabilities were introduced to remedy the situation, however. In 1999, the SQL Standards committee introduced the CASE statement.

Not everybody came onboard with it, at least not immediately. Oracle was very fond of its own DECODE function that (Oracle claimed) was more versatile and powerful. Without disputing the claim, we want to note that it finally relented, and introduced CASE expressions in version 10g (yet its beloved DECODE function is still supported, if only for backward compatibility).

Oracle's DECODE function allows you to modify the output of the SELECT statement depending on certain conditions (IF … THEN … ELSE logic). It compares an expression (usually a column value) to each search value one by one. If a match is found, the function returns the corresponding result; otherwise, it returns the default value. If no match is found and no default is specified, the function returns NULL. In addition to DECODE, Oracle 11g also has a CASE statement that is identical in usage to that of the other RDBMSs discussed here.

The CASE statement produces similar results using much less cryptic syntax, and no function is involved.

For example, in our LIBRARY database table BOOKS, the column BK_PUB_YEAR could be used to sort out “new” and “old” titles.

While it is in the eye of the beholder, such a report ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required