This online tutorial page is based on examples to make it easier to follow. The Oracle NVL2 function has 3 input parameters and it returns one of the last two variables declared in the function. Those returned variables are depending on the first function variable. The syntax of Oracle NVL2 is following:
It is important to know that you would not get confused with Oracle NVL function that has only 2 variables and it does compare between them. Oracle NVL2 compares only first variable and it returns the second variable when it is not null otherwise it returns the third variable. The following example shows how does operate Oracle NVL2 function when the first value is empty (null).
SELECT NVL2(my_value1,my_value2,my_value3) FROM (SELECT NULL AS my_value1,1 AS my_value2, 2 AS my_value3 FROM dual) ;
This SQL above returned number “2” as it has been declared as the third value. You need to keep in mind that Oracle NVL2 takes the last value in the function when the first is empty.
The next example is with the same NVL2 in Oracle, but in this time the first value is not null.
SELECT NVL2(my_value1,my_value2,my_value3) FROM (SELECT 0 AS my_value1,1 AS my_value2, 2 AS my_value3 FROM dual) ;
In this time the example has returned value number “1“. As the first value is not empty and the second declared value is number “1”.
SELECT NVL2(my_value1,my_value2,my_value3) AS nvl2_result FROM (SELECT 'Hello' AS my_value1,1 AS my_value2, 2 AS my_value3 FROM dual) ;
In short: Oracle NVL2 checks if the 1st input parameter is null or not and return the 2nd parameter when it is NOT NULL otherwise it returns your 3rd parameter. All parameters are declared by you depending what do you want to be returned.