Thursday, April 24, 2014
Home » Oracle » Articles » Oracle DECODE and CASE: What is the difference
Oracle DECODE and CASE: What is the difference

Oracle DECODE and CASE: What is the difference

PL/SQL Developer

In this article we are going to look at the similarities and differences between the Oracle DECODE function and the CASE statement. Some people say that the CASE statement is a better implementation of the DECODE function, and that “whatever you can do with a DECODE you can do with a CASE expression.

So, let us dive into some details and see what we can find out…

Introduction

The DECODE function has been around for a while, while the CASE statement was introduced in Oracle 8.1.6. The CASE statement was supposed to be superior, since it was introduced to be:

  • Easier to read
  • More flexible
  • Compatible with the ANSI standard

Still, as in almost everything when it comes to software developers, there are different “camps”, with different opinions about what is the best. In this article I will provide a lot of different information about the similarities, as well as the differences between DECODE and CASE. Hopefully, at the end of this article you will have enough information to make up what to choose in different SQL scenarios.

So, let’s start digging into the details…

Please note (if it should matter) that I am using an 11g R2 database in the samples in this article.

 

The Oracle DECODE function

Since the DECODE function has been around the longest time, let us start with it.

DECODE syntax

According to the Oracle Database SQL Language Reference, the DECODE syntax is as follows:

..where search and result operates in “pairs”, and there can be many search/result ”pairs”.

..meaning that you i.e. can have a DECODE statement like this:

In the latter sample I have deliberately formatted the DECODE statement this way, since many of the complaints about the DECODE statement is that it is easy to lose track when you have many search/result “pairs”. In my opinion, the use of DECODE is all about proper formatting…and counting of amount of parameters.

Now, let us look at the different parameters in the syntax above.

  • expression: This is the base expression that each search will be validated against
  • search: This is the possible value for expression that we are checking on
  • result: This is the value that is returned if search = expression
  • default: This is the value returned by the DECODE function if none of the given search values are equal to expression. If none is given, the DECODE function returns NULL.

Please note the following:

  • The components/arguments to the DECODE function can be any numeric or character types.
  • The maximum number of components/parameters (all added up) in the DECODE function is 255.
  • The expression value is checked against one search value at the time.
  • In the Oracle DECODE function, a so-called “short-circuit evaluation” is used. That means that as soon as one of the search values matches the expression value, the next search value is NOT validated, but the result value of the matching search value is returned.
  • DECODE can ONLY handle quality checks (“=”) between expression and search values

DECODE samples

Ok, sometimes it can be easier to see some samples, than just looking at the (at times confusing) syntax diagram. In the samples below, we are going to use the tables that comes with the SCOTT sample schema in an Oracle database installation. If you do not find these in your database, there can be several reasons for it. Here are some of the most obvious ones:

  1.  The person setting up the database (most likely the DBA) did not install the Oracle sample schemas (HR, SCOTT, etc.) when the database was set up. They can be installed afterwards if you want them. Speak with your DBA. For 11g R2 the installation of these schemas can be found here: Oracle Database Sample Schemas.
  2. The sample schemas were installed, but they are by default locked, and needs to be unlocked by your DBA

At this point I just wanted you to know that these samples are ONLY for demonstration purposes. In a real life situation we would of course join our tables, etc.

Example # 1: Setting name of department with DECODE

Let us pretend we did not have the DEPT (department table) in our schema, and we have the following query:

If we want to show the department in our query, we can use the DECODE statement like this:

Oracle DECODE limitations

It is important to point out a few limitations of the DECODE function.

Use of a range of values

As mentioned earlier, the DECODE function ONLY handles equality comparisons using the equal-to sign (“=”). If you i.e. wanted to add a column in the last query above to say that the “ACCOUNTING” and the “SALES” department were located in New York, and the “RESEARCH” and “OPERATIONS” department were located in Boston, the only way you could do this would be to handle each department code by itself, like so:

Oracle DECODE and ANSI standard

The DECODE function in Oracle is a native oracle thingy. If you i.e. would like to convert your queries to MS SQL Server (uuh…please lean forwards so that I can smack you.. ;-)  ), you would not be able to use this function. You would have to turn it into a CASE statement, since the CASE statement IS compatible with the ANSI standard.

Oracle DECODE and PL/SQL

The DECODE function can not be used within the PL/SQL language. What this means, is that if you i.e. have a PL/SQL cursor (implicit or explicit), you will not be able to use the DECODE function. The only way you could use the DECODE function in PL/SQL, would be if you defined a SQL query and used Dynamic SQL.

The Oracle CASE statement/expression

Ok, let’s go on to the next step in our comparison article: The CASE statement.

The CASE statement syntax

When it comes to the CASE statement in Oracle, there are actually two types of CASE statements:

  • Simple CASE expression
  • Searched CASE expression

Simple CASE expression

The simple CASE expression is used when you have one expression that you want to compare against a set of possible values.

In the simple CASE expression, please note the following:

  • The expression and the comparison expression must have the same data type (for character data type), or have a numeric data type. For numeric data types the common data type for the expressions are determined/implicitly converted by the argument with the highest numeric precedence
  • Oracle will search for the first comparison expression that is equal to the expression.
  • The comparison expressions must all be related

Searched CASE expression

The searched CASE expression would be used i.e. in a query where you have i.e. ranged values, conditions that are not related to each other, a condition that is actually a sub query, etc.

In the searched CASE expression, please note the following:

  • The comparison conditions do not have to be related (explained in sample below)
  • Oracle will search left to right to find an occurrence of a condition that is TRUE. Then the corresponding return expression will be returned.

 Common for both simple and searched type CASE expression:

  • With Oracle CASE , a so-called “short-circuit evaluation” is used. That means that as soon as one of the comparison condition matches the expression value, the next comparison condition is NOT validated, but the return expression of the matching search expression  is returned.
  • If none of the conditions are TRUE, default will be returned.
  • If default is not specified, NULL will be returned
  • The maximum number of components/expressions (all added up) in the CASE  is 255.
  • All of the return expressions must have the same data type (for character data type), or have a numeric data type. For numeric data types the common data type for the expressions are determined/implicitly converted by the argument with the highest numeric precedence

Oracle CASE expression samples

Finally…here are some examples of usage of the CASE expression.

CASE sample # 1: Simple CASE expression

When we discussed the DECODE function earlier, we had a sample where we wanted to show the department name. Let us use the same scenario in this CASE example:

As we can see, the same result as with DECODE.

CASE sample # 2: Searched CASE expression with range

Ok, the DECODE function kept up on the flat parts…now let’s start going uphill…. Let us now take the second scenario described under the DECODE samples above, where we wanted to show the city/location for the different departments:

There you go.

CASE sample # 3: Searched CASE expression inside a SUM

In this next sample we are using the CASE expression in a way that I very often use it in my line of work. We now want to summarize the salary for each of the departments and then group them into their cities/locations. So, basically, we want to figure out how much salary is payed out in any of our two locations.

As a step # 1, we are going to just divide the salaries into the different cities/locations that the different departments reside:

As you can see, the employee’s salary is shown in the city column for where the department exists. Also, please note that I put an NVL around the sal field to avoid any summarization issues if one of the values should be NULL. I would not work for free, but I guess someone can…

The next and last step is to do the actual summarization:

We have here used a ranged CASE expression within an aggregated SUM function.

CASE sample # 4: Searched CASE expression with non-related conditions

In this example we are going to put together a query where we are going to use a CASE expression where the conditions are not directly related to each other.

Here is the scenario for this example:

  • If the job title is “PRESIDENT” or “MANAGER”, we do not want to disclose if they have received a commission or not.
  • If an employee (other than president and mangers) has received a commission, show the value 1
  • Otherwise, show value 0

SQL> SELECT e.empno

CASE sample # 5: Searched CASE expression with sub query

In this last CASE example we are going to write a query where we are going to present a list of the departments, and then show a 1 (the number one) if that department has received a commission.

Handling of NULL values when USING CASE and DECODE

A last thing I would like to show, is the difference in the way that NULL is handled by CASE and DECODE:

As you can see above, the result is different for the two. This is one of the things that almost always seem to be  mentioned when DECODE and CASE differences are discussed. I put it here so that you can see it for yourself.

In conclusion

Ok, so in this article we have discussed some similarities, and some differences between the Oracle DECODE function, the simple CASE expression, and the searched CASE expression.

Personally I tend to use the searched CASE expression, even the times I COULD use a simple CASE expression. I guess it has to do with keeping the same standard of writing my CASE statements. I like uniformity and structure in my SQL queries, as well as in my PL/SQL code.

There are times when I use the DECODE function, but that would be only when there are very simple data comparisons.

There are people that try their best to stick to the DECODE function whenever they can, but as you probably have noticed…this article can be concluded in the following way: A CASE expression can replace a DECODE function any time, and it has a lot of additional functionality as well. The opposite is not the case ( ;-) ).

On thing you might find missing in this article is the differences in performance. I have personally not done any big comparisons, but after what I have been reading from experts on the Internet: There should not be very much difference. Also, in my work I use the CASE expressions 99% of the times, since I very often have conditions that are non-related, or have a sub-query within it.

If you have any other differences, or similarities that should have been mentioned in this article, please share with us all using the comments below.

~ TJ Abrahamsen

In this article we are going to look at the similarities and differences between the Oracle DECODE function and the CASE statement. Some people say that the CASE statement is a better implementation of the DECODE function, and that “whatever you can do with a DECODE you can do with a CASE expression. So, let ...

Review Overview

Rate this

Summary : If you enjoyed this post, rate it to let other people know what you think. Hey...don't forget to share as well :-)

User Rating: 4.6 ( 1 votes)
0

About TJ Abrahamsen

TJ Abrahamsen is the founder and author of OracleTuts. He has worked with Oracle since 1997. His expertise is with Oracle SQL and PL/SQL. He loves teaching others, and like to share his thoughts, ideas, and experience on his blog.