Three parameters are specified for the NVL2 function. The second expression (expr2) is returned if the first expression (expr1) is not NULL; the third expression (expr3) is returned if it is NULL.
Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.
For example, if a type of a is INT, b, BIGINT, and c, SHORT, then NVL2(a, b, c) returns a BIGINT type. If a type of a is INTEGER, b, DOUBLE, and c, TIMESTAMP, then NVL2(a, b, c) returns a VARCHAR type.
NVL2( expr1, expr2, expr3 )
expr2 | expr3
SELECT * FROM case_tbl;
--returning a specific value of INT type
SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;
a nvl2(a, a+1, 10.5678)