Null Handling Functions
The IFNULL, NVL, and NVL2 functions handle nulls:
IFNULL
IFNULL(v1,v2)
Operand type: Any
Result type: Any
Specifies a value other than a null that is returned to your application when a null is encountered. If the value of the first argument is not null, IFNULL returns the value of the first argument. If the first argument evaluates to a null, IFNULL returns the second argument.
For example, the SUM, AVG, MAX, and MIN aggregate functions return a null if the argument to the function evaluates to an empty set. To receive a value instead of a null when the function evaluates to an empty set, use the IFNULL function, as in this example:
IFNULL(SUM(employee.salary)/25, -1)
IFNULL returns the value of the expression sum(employee.salary)/25 unless that expression is null. If the expression is null, the IFNULL function returns -1.
Note: If an attempt is made to use the IFNULL function with data types that are not nullable, such as SYSTEM_MAINTAINED logical keys, a runtime error is returned.
Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM t1:
IFNULL Function and Decimal Data
If both arguments to an IFNULL function are decimal, the data type of the result returned is decimal, and the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the result is determined as follows:
• Precision--The largest number of digits to the left of the decimal point (precision - scale) plus largest scale (to a maximum of 39)
• Scale--The largest scale
NVL
NVL(v1, v2)
Operand type: Any
Result type: Any
Specifies a value other than a null that is returned to your application when a null is encountered. For example, in NVL(a,b), if 'a' is NULL then return 'b' else return 'a'.
NVL2
NVL2(v1, v2, v3)
Operand type: Any
Result type: Any
Returns a different value based on whether the input value is NULL or not. For example, in NVL2(a,b,c), if 'a' is not null then return 'b' else return 'c'.