Null Handling Functions
The IFNULL, NVL, and NVL2 functions handle nulls:
IFNULL
IFNULL(v1,v2)
Operand types: 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.
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 38)
• Scale—The largest scale
If the data type of both arguments to IFNULL function match, then the result will be that data type; if there is just a size difference then the larger will be used.
If the two arguments are of differing data types, they must be comparable. For a description of comparable data types, see Assignment Operations. This includes any collation type associated with the parameters; if differing collations are involved, a cast may be needed to make them compatible.
When the arguments are different but comparable data types, the following rules are used 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 > float8 > float4 > decimal >bigint> integer > smallint > tinyint
and
nchar > nvarchar > c > text > char > varchar > byte > byte varying
• The result length is taken from the longest value. For example: IFNULL with VARCHAR(10) and C(5) results in C(10).
Usually the first argument will be nullable but whether the function can return a null value depends only on the second argument; the result will only be nullable if a second argument is also nullable.
NVL
NVL(v1, v2)
Operand types: Any
Result type: Derived from v1
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'.
The data type returned will be that of the first parameter, possibly with a length increase and made nullable if the second parameter is be nullable.
NVL2
NVL2(v1, v2, v3)
Operand types: Any
Result type: Derived from v2
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'.
The data type returned will be that of the v2 parameter, possibly with a length increase for v3 and made nullable if either v2 or v3 parameter is nullable.
UUID ([expr]) Function
With no parameters, the UUID() function creates a 128-bit universal unique identifier (UUID).
> createdb uuiddb
> sql uuiddb
* CREATE TABLE uuidtable (u1 UUID, u2 BYTE(16));
* INSERT INTO uuidtable VALUES (UUID(), UUID());
//
// Verify the length in byte format
//
* SELECT LENGTH(u1) FROM uuidtable;
//
//Length of u1 will be 36 bytes representing the length of the ASCII representation
//Length of u2 will be 16 bytes, the length of the BYTE(16) representation
//
With a parameter, UUID (expr) will convert that parameter to a UUID if possible. Character string data types must follow the 36-byte ASCII HEX format including the hyphens: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
UUIDs stored as BYTE(16) can be readily converted into the UUID data type.