Using decode and case functions

 

The most powerful of all of the BIFs are the decode and case functions.  The decode and case functions are used within the Oracle database to transform data values for one value to another. 

 

One of the most amazing features of the case the decode statements is that they allow us to create an index on data column values that do not exist in the database.

 

Oracle started with the decode statement and later refined it in Oracle9i, morphing it into the case statement.

 

 

Let’s take a look at how the decode statement works.  The decode statement was developed to allow us to transform data values at retrieval time.  For example, say we have a column named REGION, with values of N, S, W and E.  When we run SQL queries, we want to transform these values into North, South, East and West.  Here is how we do this with the decode function:

 

 

Note that decode starts by specifying the column name, followed by set of matched-pairs of transformation values.  At the end of the decode statement we find a default value.  The default value tells decode what to display if a column values is not in the paired list.

 

We can also use the decode statement to count the number of distinct values within a data column, such as the report below: 

 

 

Here is the SQL to create this report in your pubs database:

 

 

 

This is a very important SQL statement because it demonstrates the nesting of BIFs and the use of decode for counting values.  Let’s take a closer look.

 

1 – Transformation of publisher name - In the above SQL we see that we are selecting the first 20 characters of the pub_name (substr(pub_name,1,20)) and then passing this result to the initcap function to make the words display in title case.

 

initcap(          ç change to title case

   substr(        ç take the sub-string

      pub_name,   ç of the pub_name column

      1,          ç starting at column 1

      20          ç for 20 columns

      )

)

 

 

2 – Transformation  of book type - When we decode book_type, note that we are transforming the value to a 1 if the column is found, and a 0 if it is not found. 

 

decode(

   book_type,         ç Column name

   ‘computer, 1       ç Matched pair – if computer, then 1

   0                  ç default value if not computer

   )

 

Once we have converted the column to a 0-1 numeric value, we pass the entire clause to the sum function, and add-up the numbers.

 

As we can see, the decode function is convoluted and hard to write.  Oracle added the case function to SQL starting in Oracle9i to simplify this type of data transformation. The case statement is an easier for of the decode statement.  In its simplest form, the case statement is used to return a value when a match is found:

 

 

 

In the above example, we see that we display “Low” when the commission percent is .1, “Average” when it is .15, and “High” when it is .2.  This syntax is far more readable than the cryptic decode function.

 

The case statement can also be used to perform complex Boolean matches. A more complex version is the Searched CASE expression where a comparison expression is used to find a match:

 

 

 

In this example we see that we test for specific job titles and salaries, and display a percentage based upon these values.