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:
Ifnull Result Data Type
If the arguments are of the same data type, the result is of that data type. If the two arguments are of different data types, they must be of comparable data types.
When the arguments are of different but comparable data types, the DBMS Server uses the following rules to determine the data type of the result:
• The result type is always the higher of the two data types; the order of precedence of the data types is as follows:
date > money > float4 > float > decimal > integer > smallint > integer1
and
c > text > char > varchar > long varchar > byte > byte varying > long byte
• The result length is taken from the longest value. For example:
ifnull (varchar (5), c10)
results in c10.
The result is nullable if either argument is nullable. The first argument is not required to be nullable, though in most applications it is nullable.
IFNULL 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
Last modified date: 11/28/2023