Introduction

Definitions

Basic Usage

Logic-driven Column Calculations

Mind the Trap!

Beyond Equality

Multi-part Logic

User-definable Report Ordering

A Real-world Example

The Down-side

The Up-side

Please note this page has moved to http://oracledoug.com/decode10.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

The Down-side

Before we summarise the strengths of the DECODE function, let's focus on some of the potential weaknesses if we don't use it appropriately. Most of these are related to coding style and are therefore under our control to a certain extent. The first is that of code readability. It should be clear from Example 7 that clean and readable code formatting is essential when using a number of DECODEs, particularly if they are nested. The example I have shown was one of the smaller queries from the application concerned and when some individual queries grew to over 50 lines, they could become difficult to understand or maintain. The best approach is to develop clear coding standards from the start, which should include some form of alignment of indentations to make the individual components of the DECODE functions very clear.

The second, which was mentioned earlier, is that DECODE is a post-retrieval function and it is easy to write code which is spectacularly inefficient but functionally correct. As a first principle for any SQL statement you may write, you should attempt to reduce the rows retrieved using the best access path and the most selective WHERE clause. Only then should you use DECODE for additional processing.

The final potential problem with DECODE is that it is an Oracle-specific extension and is not included in the ANSI standards. This is a problem if code-portability to other databases is an issue, but shouldn't distract you from the extra power in the Oracle implementation. Many of us use PL/SQL, after all!

Previous

Next