Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Comparison Functions / Oracle DECODE Function

Oracle DECODE Function

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

Introduction to Oracle DECODE() function

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

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

SELECT DECODE(1, 1, 'One') FROM dual;

It works like the following if statement

IF 1 = 1 THEN RETURN 'One'; END IF

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

SELECT DECODE(1, 2, 'One') FROM dual;

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:

SELECT DECODE(1, 2, 'One','Not one') FROM dual;

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

IF 1 = 2 THEN RETURN 'One'; ELSE RETURN 'Not one'; END IF;

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

SELECT DECODE(2, 1, 'One', 2, 'Two') FROM dual;

The result is:

Two

In this example, the function compares the first argument (2) with the second one. If the first argument equals 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 2 = 1 THEN RETURN 'One'; ELSIF 2 = 2 THEN RETURN 'Two'; END IF;

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

SELECT DECODE(3, 1, 'One', 2, 'Two', 'Not one or two') FROM dual;

The query returned:

Not one or two

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

IF 3 = 1 THEN RETURN 'One'; ELSIF 3 = 2 THEN RETURN 'Two'; ELSE RETURN 'Not one or two'; END IF;

Oracle DECODE() function syntax

The following illustrates the syntax of the Oracle DECODE() function:

DECODE (e , s1, r1[, s2, r2], ...,[,sn,rn] [, d]);

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 DECODE() 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), the result (r), and default value (d) in the DECODE() function. The DECODE() 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 DECODE() function never evaluates a search (si+1) when a previous search (si) equals e.

Oracle DECODE() function examples

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

A) Use DECODE() function to make data more meaningful

See the following locations table in the sample database:

locations table

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

SELECT country_id, COUNT(*) FROM locations GROUP BY country_id HAVING COUNT(*) >= 2 ORDER BY country_id;
Oracle DECODE function - locations

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

SELECT DECODE(country_id, 'US','United States', 'UK', 'United Kingdom', 'JP','Japan' , 'CA', 'Canada', 'CH','Switzerland', 'IT', 'Italy', country_id) country , COUNT(*) FROM locations GROUP BY country_id HAVING COUNT(*) > =2 ORDER BY country_id;
Oracle DECODE function example

B) Oracle DECODE with ORDER BY example

Consider the following employees table:

employees table

The following query uses the DECODE() function in the ORDER BY clause to sort the employees result set based on an input argument:

SELECT first_name, last_name, job_title FROM employees ORDER BY DECODE('J', 'F', first_name, 'L', last_name, 'J', job_title);
Oracle DECODE function with ORDER BY example

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

Oracle DECODE with GROUP BY example

See the following products table:

products table

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

WITH list_prices AS( SELECT ROUND(AVG(list_price),2) average FROM products ) SELECT DECODE( SIGN( (list_price - average ) ), 1, '> Average of ' || average , 0, 'Average', -1, '< Average of ' || average) list_price, COUNT(*) FROM products, list_prices GROUP BY DECODE( SIGN( (list_price - average ) ), 1, '> Average of ' || average , 0, 'Average', -1, '< Average of ' ||average );
Oracle DECODE function with GROUP BY example

Oracle DECODE() function with SUM() example

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

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

SELECT 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" FROM products INNER JOIN product_categories USING (category_id) GROUP BY category_name;

Here is the output:

Oracle DECODE function with SUM example

Oracle DECODE() function and NULL

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

The following statement returns the string Equal:

SELECT DECODE(NULL,NULL,'Equal','Not equal') FROM dual;

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

  • Was this tutorial helpful?
  • YesNo
Next Oracle COALESCE Function

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.