NVL2

Syntax

NVL2(expr1, expr2, expr3)

Overview

The NVL2 function returns the value of the second argument if the first argument is non-NULL, and the value of the third argument if the first argument is NULL.

The second and third arguments must be types of the same category, and there is a separate casts function that performs implicit casting if necessary.

Parameters

Parameter	Description
expr1	The first value to evaluate. If this value is non-NULL, it is the basis for returning the second argument.
expr2	The value that will be returned if the first argument is not NULL.
expr3	The value that will be returned if the first argument is NULL. The types of the second and third arguments must match, and casting is performed if necessary.

Example

# test data
create table employees (
  first_name varchar,
  last_name varchar,
  salary integer,
  hire_date timestamptz,
  commission_pct integer,
  bonus integer,
  status varchar
);

INSERT INTO employees (first_name, last_name, salary, hire_date, commission_pct, bonus, status) VALUES
('John', 'Doe', 55000, '2020-03-15 09:00:00', NULL, 2000, 'Active'),
('Jane', 'Smith', 62000, '2019-07-22 10:30:00', 10, 3000, 'Active'),
('Michael', 'Johnson', 72000, '2018-11-10 08:45:00', 7, 2500, 'Inactive'),
('Emily', 'Davis', 48000, '2021-05-01 12:00:00', NULL, 1500, NULL),
('David', 'Wilson', 53000, '2017-09-17 14:20:00', 8, 2200, NULL),
('Sarah', 'Brown', 60000, '2016-12-05 09:15:00', 12, 2800, 'Active'),
('James', 'Taylor', 75000, '2015-06-30 16:45:00', NULL, 2600, 'Resigned'),
('Jessica', 'Martinez', 68000, '2022-01-25 11:10:00', 5, 1800, NULL),
('Daniel', 'Anderson', 58000, '2020-10-05 13:35:00', 6, 2000, 'Active'),
('Laura', 'Thomas', 49500, '2023-08-12 08:00:00', 4, 1200, 'Probation');

# test 1
-- return bonus if commission_pct value is not NULL, and 0 if NULL
SELECT oracle.NVL2(commission_pct, bonus, 0)
FROM employees;

 nvl2 
------
    0
 3000
 2500
    0
 2200
 2800
    0
 1800
 2000
 1200
(10 rows)

# test 2
-- return the Second argument (e.g., 'Active') If the first argument is not NULL, and the third argument (e.g., 'Inactive') if NULL
SELECT oracle.NVL2(status, 'Active', 'Inactive')
FROM employees;


   nvl2   
----------
 Active
 Active
 Active
 Inactive
 Inactive
 Active
 Active
 Inactive
 Active
 Active
(10 rows)