Oracle Decode Variable Assignment

Summary: in this tutorial, you will learn how to use the Oracle function to embed if-then-else logic in SQL queries.

Introduction to Oracle function

The Oracle function allows you to add the procedural if-then-else logic to the query.

In the following example, the Oracle function compares the first argument (1) with the second argument (1). Because they are equal, the function returns the second argument which is the string One.

It works like the following statement

The following example is slightly different from the one above. The query returns a null value because one does not equal two.

If you want to specify a default value when the first argument is not equal to the second one, you append the default value to the argument list as shown below:

It works like the following if-then-else statement:

What if you want to compare the first argument with a list of arguments? See the following example:

The result is

In this example, the function compares the first argument (2) with the second one. If the first argument equal the second one, the function returns the third argument (One). Otherwise, it compares the first argument with the fourth argument (2). If they are equal, the function returns the fifth argument (Two).

It works like the following if-then-elsif statement:

If you want to specify a default value when the function does not find any match, you do it as follows:

The query returned:

The query works like the following if-then-elsif-else statement:

Oracle function syntax

The following illustrates the syntax of the Oracle function:

Arguments

e

The first argument e is the value to be searched. The function automatically converts e to the data type of s1 before comparing.

s1, s2, .. sn

The s1, s2, … or sn is an expression to search for. Note that s2, s3, … sn are automatically converted to the data type of s1 before comparing.

r1, r2, .. rn

The r1, r2, …, or rn is the expression to return when e is equal to s.

d

d is an expression to return when e does not equal to any searched value s1, s2, .. sn.

Return value

The function returns a value with the data type of the first result (r1, r2, .. rn or d) argument.

Note

You can use expressions for the search (s), result (r), and default value (d) in the function. The function evaluates each search value (s1, s2, .. or sn) only before comparing it to the first argument (e), rather than evaluating all search values. In other words, the function never evaluates a search (si+1) when a previous search (si) equals e.

Oracle function examples

Let’s take some examples of using the function to see how it works.

A) Use function to make data more meaningful

See the following table in the sample database:

The following statements return the country that has at least two locations stored in the database:

The country id is quite cryptic. You can use the function to make the country data more meaningful as follows:

B) Oracle DECODE with ORDER BY example

Consider the following table:

The following query uses the function in the clause to sort the employees result set based on an input argument:

In this example, we sorted the employee list by job title because we passed the character as the first argument of the function.

Oracle DECODE with GROUP BY example

See the following table:

The following statement illustrates how to use the function in the clause. It returns the number of products whose list prices are higher than, equal to, and less than the average list price.

Oracle function with example

We will use the and tables in this example for the demonstration.

The following example uses the function to change the ranges to the binary number and uses the function to count the number of values with a specified range:

Here is the output:

Oracle function and NULL

NULL cannot be compared to anything even NULL. However, function treats two null values are equal.

The following statement returns the string Equal:

In this tutorial, you have learned how to use the Oracle function to add procedure if-then-else logic to SQL queries.

  • Was this tutorial helpful?
  • yes   no
  DECODE(1,1,'One')
    RETURN'One';
  DECODE(1,2,'One')
  DECODE(1,2,'One','Not one')
    RETURN'One';
    RETURN'Not one';
  DECODE(2,1,'One',  2,'Two')
     RETURN'One';
    RETURN'Two';
  DECODE(3,1,'One',  2,'Two','Not one or two')
     RETURN'One';
    RETURN'Two';
    RETURN'Not one or two';
DECODE(e,s1,r1[,s2,r2],...,[,sn,rn][,d]);
  COUNT(*)>=2
  DECODE(country_id,'US','United States','UK','United Kingdom','JP','Japan'
  ,'CA','Canada','CH','Switzerland','IT','Italy',country_id)country,
  COUNT(*)>=2
  DECODE('J','F',first_name,'L',last_name,'J',job_title);
    SELECT
      ROUND(AVG(list_price),2)average
    FROM
      products
  DECODE(SIGN((list_price-average)),
  1,'> Average of '||average,
  0,'Average',
  -1,'< Average of '||average)list_price,
  DECODE(SIGN((list_price-average)),
  1,'> Average of '||average,
  0,'Average',
  -1,'< Average of '||average);
  category_name,
  SUM(DECODE(GREATEST(list_price,0),LEAST(list_price,1000),1,0))"< 1000",
  SUM(DECODE(GREATEST(list_price,1001),LEAST(list_price,2000),1,0))"1001-2000",
  SUM(DECODE(GREATEST(list_price,2001),LEAST(list_price,3000),1,0))"2001-3000",
  SUM(DECODE(GREATEST(list_price,3001),LEAST(list_price,8999),1,0))"3001-8999"
INNERJOINproduct_categoriesUSING(category_id)
  category_name;
  DECODE(NULL,NULL,'Equal','Not equal')

 

 

 

 

Oracle decode function

Oracle Database Tips by Donald Burleson


Using Oracle decode function

The most powerful of all of the BIFs are the Oracle decode and Oracle case function. The Oracle 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 Oracle 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 Oracle 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 Oracle 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.

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

select
initcap(substr(pub_name,1,20)) publisher_name,
sum(decode(book_type,'computer',1,0)) computer,
sum(decode(book_type,'fiction',1,0)) fiction,
sum(decode(book_type,'management',1,0)) management,
sum(decode(book_type,'miscellaneous',1,0)) miscellaneous,
sum(decode(book_type,'music',1,0)) music
from
publisher p,
book b
where
p.pub_key = b.pub_key
group by
pub_name
;

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.

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 form of the decode statement.

The power of these functions become apparent when combined with the decode built-in function. By using decode to change the ranges to a binary number, we can then use the sum built-in function to count the number of values within a specified range. The query below shows how this works:

prompt Salary ranges by Department

select

deptno,

sum(decode(greatest(SAL,3001), least(SAL,9999), 1, 0)) "$3001-$9999",

sum(decode(greatest(SAL,2001), least(SAL,3000), 1, 0)) "$2001-$3000",

sum(decode(greatest(SAL,1001), least(SAL, 2000), 1, 0)) "$1001-$2000",

sum(decode(greatest(SAL, 0), least(SAL, 1000), 1, 0)) "< $1000"

from

emp

group by

deptno;
 

Oracle documentation notes the following syntax for decode:


DECODE(expr, search, result [, search, result ]... [, default ] )

 

 

 

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 -  2017

All rights reserved by Burleson

Oracle

® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

Categories: 1

0 Replies to “Oracle Decode Variable Assignment”

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *