Oracle NVL2

In this article, we will learn how to use NVL2 function and how is it different from existing NVL function.

NVL2 function is used to substitute other values based on whether our expression is NULL or not.

Syntax

The syntax of NVL2 function is as follows:

NVL2 ( expression1, e2, e3)

Here,

expression1 – It is the value we are going to replace based on whether it is NULL or not.

e2 – If expression1 is NOT null then we substitute expression1 with e2.

e3 – If expression1 is NULL then we we substitute expression1 with e3.

In this syntax, expression1 could be of any data type. If data type of e2 and e3 are different then Oracle converts the data type of either e2 or e3 for comparison purpose. If it can’t be converted then Oracle returns an error.

NVL2 Function Examples

1. NVL2 Function with Numbers

Lets see how NVL2 works by taking two numeric data types for comparison:

SELECT
NVL2(NULL, 20, 40) -- 40
FROM
dual;

Result:

Since, the expression1 in this case is NULL, it returns e3 or 40 in this case.

2. NVL2 Function with Text Data Type

We can also use NVL with text data type, lets have a look at the following example:

SELECT
NVL2(1, 'DOG', 'CAT')
FROM
dual;

Result:

As the expression1 is 1 (not null) in this case, the query returns e3 or ‘cat’ in this case.

3. Using NVL2 on a Column for performing calculation

For this example, we will be using employee table from HR schema. Earlier, we used NVL function to calculate total salary of employees, this time we will be using NVL2.

There are many NULL values in commission_pct column as not every employee gets a commission on salary, for this reason we will be using NVL2 function. Lets see the query:

SELECT first_name, last_name,
NVL2(commission_pct, (salary + salary *commission_pct),salary )Total_Salary
FROM EMPLOYEES ;

Result:

The NVL2 function checks whether value in commission_pct is NULL or not. If its not null, then it returns e2 expression which adds commission_pct value to salary. If its NULL, then it returns e3, which is just salary in this case.

Using this NVL2 function we are able to accurately calculate the total salary of employees and eliminate all null values.

NVL2 and CASE Function

NVL2 function can also be replaced with a CASE function. Here’s the logical alternative to the NVL2 function that we just used.

CASE
WHEN e1 IS NOT NULL THEN
e2
ELSE
e3
END

NVL2 function is much easier to type and use and performs the same operation as the CASE function query used above.

In this example, we learned how to use NVL2 function for substituting NULL values in an expression with other alternatives.