RelationalDBDesignRelationalDBDesign



Data Manipulation   «Prev  Next»
Lesson 8 DECODE and NVL functions
Objective Modify query results using the DECODE and NVL functions.

DECODE and NVL Functions

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.

DECODE

Think of DECODE as a mini-IF-statement. It 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.

  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.

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.