NVL2

The NVL2 function returns different values based on whether an expression is null. If expr1 is not null, the value of expr2 is returned. If expr1 is null, the value of expr3 is returned. If the types of expr2 and expr3 are different, expr3 is converted to the type of expr1.

Syntax

NVL2(expr1, expr2, expr3)

Parameters

If the data types of expr2 and expr3 are different, ApsaraDB for OceanBase implicitly converts one data type to the other data type. If the data types cannot be implicitly converted, the database returns an error. If expr2 is character or numeric data, the following implicit conversion rules are used:

• If expr2 is the data of the CHAR, NCHAR, NVARCHAR, VARCHAR2, or VARCHAR character type, ApsaraDB for OceanBase converts expr3 to the data type of expr2 before the value is returned unless expr3 is NULL. In this case, the data types do not need to be converted, and the database returns VARCHAR2 in the character set of expr2.

• If expr2 is the data of the NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBL numeric type, ApsaraDB for OceanBase determines the parameter that has the highest numeric precedence. ApsaraDB for OceanBase implicitly converts the other parameter to this data type and returns the data type.

Return type

If expr1 and expr2 are NULL, NULL is returned. If expr1 is the data of the CHAR, NCHAR, NVARCHAR, VARCHAR2, or VARCHAR character type, VARCHAR2 in the character set of expr1 is returned. If expr1 is the data of the NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE numeric type, the data type that has the highest numeric precedence in expr1 is returned.

Examples

Assume that data is inserted into the employee name column name , the salary name salary , and the commission column commission_pct in the EMPLOYEES table. Execute the following statements:

CREATE TABLE EMPLOYEES (name VARCHAR(20),commission_pct numeric);
INSERT INTO EMPLOYEEs VALUES ('Baer', 10000, null);
INSERT INTO EMPLOYEEs VALUES ('Bada', 2800, null);
INSERT INTO EMPLOYEEs VALUES ('Boll', 5600, .25);
INSERT INTO EMPLOYEEs VALUES ('Bates', 7300, .39);
INSERT INTO EMPLOYEEs VALUES ('Broll', 4000, null);

Use the NVL2 function to query the total income of employees. If the commission_pct column is not empty for an employee, the income of the employee consists of the salary and the commission. Otherwise, the income of the employee is only the salary. Execute the following statement:

SELECT name, salary,NVL2(commission_pct, salary + (salary * commission_pct), salary) income 
FROM employees WHERE name like 'B%' ORDER BY name;

The following query result is returned:

+--------------+----------------+----------------+
|        NAME  |        SALARY  |        INCOME  |
+--------------+----------------+----------------+
|        Bear  |         10000  |         10000  |
+--------------+----------------+----------------+
|        Bada  |          2800  |          2800  |
+--------------+----------------+----------------+
|        Boll  |          5600  |          7280  |  
+--------------+----------------+----------------+
|       Bates  |          7300  |         10220  |
+--------------+----------------+----------------+
|       Broll  |          4000  |          4000  |
+--------------+----------------+----------------+