4. Elements of OpenSQL Statements : Functions : Ifnull Function
 
Share this page                  
Ifnull Function
The ifnull function specifies a value other than a null that is returned to your application when a null is encountered. The ifnull function is specified as follows:
ifnull(v1,v2)
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 that 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, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
select col1, ifnull(col2, 0), left(col4, 22) from t1: