Preparing data for analysis often means encountering some shortcomings within the data itself. Extra spaces, null values, blank values, wrong data types and duplicate values all stand in the way of properly evaluating data – that’s where the NVL and NVL2 functions can help make report output more meaningful.

For example, you are asked to provide a report of employees of the company. For some employees, the secondary contact number is NULL. Instead of showing NULL, we must display some text like ‘Contact Not Available.’ You can achieve it using CASE, NVL, NVL2, COLEASE, DECODE functions. Among all these functions, we are going to learn about the NVL() and NVL2() functions.

Environment setup

I have installed Oracle 21c express edition on my workstation and created a pluggable database named EltechDB. I have created a table named ProductCatalogue in the database. The following query creates the ProductCatalogue table.

CREATE TABLE PRODUCTCATALOGUE
  (
     PRODUCT_ID          NUMBER,
     PRODUCT_NAME        VARCHAR2(255 CHAR),
     UNIT_PRICE          NUMBER(10, 2),
     PRODUCT_DESCRIPTION VARCHAR2(500 CHAR),
     TAX                 NUMBER
  ) 

Run the following query to insert dummy data in it.

Now let us take a look to understand how the NVL() function works.

Understanding the NVL() function

The NVL function replaces the null value with some meaningful value. The syntax is the following:

SELECT NVL(expression_1, expression_2)

If expression_1 evaluates as NULL, it returns the value specified in expression_2. If expression_1 evaluates as NOT NULL, the function returns the value specified in expression_1.

Note that if the datatype of expression_1 and expression_2 are different, then Oracle performs an implicit conversion. If Oracle cannot perform the implicit conversion, it returns an error.

NVL () Example 1

Expression_1 evaluates as NULL in the following code, so the NVL() returned N/A (expression_2). Let us see another example.

SQL> select NVL(NULL, 'N/A') from dual;

NVL

—

N/A

Expresion_1 evaluates as NOT NULL in the following code, so the NVL() returned ‘Nisarg’ (expression_2).

SQL> select NVL('Nisarg','N/A') "Employee Name" from dual;

Employ

——

Nisarg

Example 2: Example with ProductCatalogue table

We are using the ProductCatalogue table of the EltechDB database. First, let us run the query to populate the list of the Products. Run the following query

select product_id,product_name,unit_price,product_description from productcatalogue

As you can see in the query output, the table has four records with a NULL value in the Product_description column. Instead of showing NULL, we want to show the text ‘Product description is not available.’ To do that, the SQL query should be written as follows

select product_id,product_name,unit_price,NVL(product_description,'Product description is not available')”Product Description” from productcatalogue;

As you can see, the NULL values have been replaced with ‘Product description is not available.’ text. Let us take another example. There are a few records with NULL in the Tax column. Instead of displaying NULL, I want to display zero. To do that, run the following query:

select product_id,product_name,unit_price,NVL(product_description,'Product description is not available')"Product Description", NVL(tax,0)"Tax on Product" from productcatalogue;

As you can see in the above screenshot, the NULL values in the Tax column have been replaced with zero.

Now, let us understand how the NVL2() function works

Understanding the NVL2() Function

The NVL2() function is an extended version of the NVL() function of the Oracle database. The syntax of the NVL2() function is as follows:

SELECT NVL(expression_1, expression_2, expression_3)

The NVL2 function evaluates expression_1. If expression_1 evaluates as NULL then it returns expression_3 and if expression_1 evaluates non-null values, the function returns expression_2.

NVL2() Example 1

Expression_1 evaluates as NULL in the following code, so the NVL2() returns Employee Does not exist (expression_3).

SQL> select NVL2(NULL,'S02263','Employee Does not exist') "Employee Name and Code" from dual;

Employee Name and Code

————————

Employee Does not exist

The following code, the expression_1 evaluates as NOT NULL, so the NVL2() returned ‘S02263’ (expression_2).

SQL> select NVL('Nisarg','N/A') "Employee Name" from dual;

Employ

——

Nisarg

Example 2: Example with ProductCatalogue table

We want to create a report using a query, and the logic is as follows:

1. If the value in the tax column is NULL, then zero must be displayed. Also, the query should return the value of unit_price. See the following image for reference.

2. If the value in the tax column is NOT NULL value, then the query must calculate the percentage of the value in the tax column and display the final output by doing a sum of the unit_price and the calculated tax. See the following image for reference.

To generate a report based on the above logic, the final query should be written as follows:

select product_id, product_name,unit_price, NVL(tax,0)"Tax on Product", NVL(NVL2(NVL(tax,0),((tax/100)*unit_price)+unit_price,unit_price),unit_price)"Final Price"

from productcatalogue

Below is the output.

As you can see, for the product_id =9, the query has returned the final price without calculating the tax. For the product_id=7, the query has returned the final price by calculating the tax and unit_price.

Summary

In this article we learned about the NVL() and NVL2() functions and their usage with a few examples, useful for beginners who want to learn the advanced functions of PL/SQL programming.

Related Links

Blog: Allowing Only One NULL

Blog: How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Blog: Four functions every data preparation tool should offer