Data Manipulation   «Prev  Next»

Lesson 8 DECODE and NVL functions
Objective Modify query results using the DECODE and NVL functions

DECODE Function in Oracle

What is the purpose of the DECODE Function in Oracle?
The DECODE function in Oracle is a versatile and powerful tool that allows users to perform conditional transformations on data within an SQL query. It is similar to the CASE statement, but it offers a more compact syntax, making it a popular choice for simplifying complex queries and improving code readability.
The primary purpose of the DECODE function is to evaluate a given expression, compare it to a set of search values, and return the corresponding result value based on the match. In essence, it provides a convenient way to perform if-then-else logic within a query, thus enabling users to manipulate and transform data as per specific conditions.
The syntax for the DECODE function is as follows:
DECODE(expression, search1, result1, [search2, result2, ...], [default])

Parameters:
  1. expression: The value or column to be evaluated.
  2. searchN: The search value to compare with the expression.
  3. resultN: The corresponding result value to be returned if the expression matches the search value.
  4. default: Optional parameter that specifies the value to be returned if no match is found.

The DECODE function compares the expression with each searchN value sequentially. If a match is found, it returns the corresponding resultN value. If no match is found and a default value is provided, the function returns the default value; otherwise, it returns NULL.
Example Usage:
Suppose we have a table named employees with the following columns: employee_id, first_name, last_name, and job_code. We want to retrieve the full names of all employees along with a description of their job titles.
Here is a sample query using the DECODE function:
SELECT first_name || ' ' || last_name AS full_name,
       DECODE(job_code, 
              'MGR', 'Manager',
              'DEV', 'Developer',
              'HR', 'Human Resources',
              'Unknown') AS job_title
FROM employees;

In this example, the DECODE function evaluates the job_code column, comparing it with the specified search values ('MGR', 'DEV', and 'HR'). If a match is found, the corresponding job title is returned. If there is no match, the function returns 'Unknown'.


DECODE and NVL Functions


Traditional Programming Logic

In programming and logic, a common construction of a problem is in the pattern
if (temp >32) 
then
  [ice is melting]
else
  [ice is solid]

DECODE follows this kind of logic. Basic structure and usage of DECODE.
DECODE(value, if1, then1, if2, then2, if3, then3, . . . ,else)

The DECODE and NVL functions are really special. They can be used for date, number, and character datatypes. I have found these two functions to be great time-savers when writing logic using Oracle SQL.

DECODE

Think of DECODE as a mini-IF-statement, which can compare the value in a column or expression to a list of values and substitute a different value for each item on the list. The basic syntax is:

DECODE(col1,case1,value1 [, case2, value2,...] , 
defaultvalue) 

Add as many case and value pairs as you need. The final value is used when col1's value does not match any of the cases.
The MouseOver below shows an example of DECODE. Mouseover the state of Hawaii HI.

Decode NVL Function
  1. The column the DECODE function examines is named STATE.
  2. The first case looks at the value in STATE and compares it to this value (the literal 'HI').
  3. If STATE is equal to 'HI', then the DECODE function returns the literal shown here.
  4. The second case is considered if STATE does not contain 'HI.' Here, STATE is compared to 'AK.'
  5. If STATE equals 'AK,' the DECODE function returns this value.
  6. This is the final case. It applies to all rows where the value in STATE is neither 'HI' nor 'AK.' In these cases, the value shown here gets returned by the DECODE function.
  7. For your information, this name is the column alias. It is used as the column heading.
  8. Here are the results when using the CUSTOMER table in the pet store schema.

SELECT STATE,
DECODE(STATE,'HI', 'Remote US state',
             'AK', 'Continental US state',
             'Contiguous US state') LOCATION 
FROM CUSTOMER
The column the DECODE function examines is named STATE

Oracle Decode Example
You can also use DECODE to create columns of summarized data in a single query.

NVL

NVL is a simple function that replaces a null value with the specified value. It is useful when you want to perform arithmetic on columns that might contain null values. In these cases, a null value will cause the arithmetic to result in a null value. To avoid this, you can substitute zero for null value using the NVL function. Values that are anything other than null remain unchanged. The syntax is shown below.

NVL syntax
When you execute the query, you see a list of all the products. This is a partial listing of the query result.
NVL (coll, value)
SELECT PRODUCT_ID, PACKAGE_ID, NVL(PACKAGE_ID, 0) 
FROM PRODUCT

Decode Nvl Functions - Exercise

Click the Exercise link below to write a query using DECODE and NVL.
Decode NVL Functions - Exercise
The next lesson concludes this module.