How To Use NVL2 In Oracle

How To Use NVL2 In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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:

NVL2 (<input_value>,<return_when_NOT_NULL>,<return_when_NULL>);

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) ;

dba oracle dba oracle database management system database oracle performance tuning oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql oracle nvl oracle online courses

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) ;

dba oracle dba oracle database management system database oracle performance tuning oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql oracle nvl oracle online courses

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) ;

dba oracle dba oracle database management system database oracle performance tuning oracle nvl2 oracle nvl2 in oracle sql nvl2 oracle sql nvl2 in oracle sql oracle nvl oracle online courses

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.

See Also:
Oracle Select Oracle NVL Oracle Decode Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*