Returning one of expressions depending on whether check expression is NULL or NOT NULL

This chapter describes functions used in various databases to return one of two expressions depending on whether a specified check expression is NULL or NOT NULL, and their conversion by SQLWays.

Note. These functions differ from functions like ISNULL, COALESCE etc. that return the first non-null value.

TABLE 57. Returning one of expressions depending on whether check expression is NULL or NOT NULL

Database	Syntax	Description
Oracle	NVL2 (exp1, exp2, exp3)	NVL2 lets you determine the returned value depending on whether a specified expression is NULL or NOT NULL.   If exp1 is NULL value, then the value of exp2 is returned. If exp1 is not NULL, the value of exp3 is returned. The argument exp1 can have any data type.   If the data types of exp2 and exp3 are different, then Oracle converts exp3 to the data type of exp2 before comparing them unless exp3 is a NULL constant   The data type of the return value is always the same as the data type of exp2, unless exp2 is character data, in this case the returned value's data type is VARCHAR2.
Sybase Adaptive Server Anywhere	IFNULL (exp1, exp2 [,exp3])	IFNULL lets you determine the returned value depending on whether a specified expression is NULL or NOT NULL.   If exp1 is NULL value, then the value of exp2 is returned. If exp1 is not NULL, the value of exp3 is returned. If exp1 is not NULL and there is no exp3, NULL is returned.
Microsoft SQL Server	-	Not supported.