Question: What is
the difference between the decode and case operators in
Oracle SQL? Can you illustrate the difference between
case and decode with an example?
Answer: The difference between
decode and case are straightforward. While the decode
operator has been around since the earliest days of Oracle,
the case operator was introduced in Oracle 8.1.6.
While d3code and case can be used interchangeably, the
decode is more powerful because decode can change SQL
results. See my notes on the
decode function. The case statement is used for
conditional power in SQL statements. See my notes on
the
case clause.
The case function is a much better option because case
is:
- More flexible than DECODE
-
Easier to read
- ANSI-compatible
However, CASE is essentially a better implementation of
DECODE so the reasons for using either are similar.
Compare
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:
select
decode (
region,
'N','North',
'S','South',
'E','East',
'W','West',
'UNKNOWN'
)
from
customer;
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.
The decode function is considered the most powerful
function in Oracle. Oracle 8i release introduced the
CASE expression. The CASE expression can do all that
DECODE does plus lot of other things including IF-THEN
analysis, use of any comparison operator and checking
multiple conditions, all in a SQL query itself.
Moreover, using the CASE function, multiple conditions
provided in separate SQL queries can be combined into
one, thus avoiding multiple statements on the same
table.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
