If the value for a column used in an expression is null, if no “null handling” method is coded in an expression, any null column value passed into an expression causes an undefined result (NULL ExprException) at runtime. These error messages are issued to alert the user that this has happened because you may get results you do not want. In some cases, for example, when the target allows nulls, the result is acceptable.
When writing expressions, if the user encounters the warning message that indicates “nulls allowed” columns are being used and undefined results are possible, the expression language offers a solution. The following system methods are available for “handling” or converting NULL values (if they occur) and avoiding “undefined” results in the expressions: nulltodecimal, nulltodouble, nulltointeger, and nulltostring.
Except for the datatype, these all work the same:
-
The first operand is a column and the second operand is a value to be returned if the value for the column is ever NULL;
-
If the column is not null, then the column value is returned.
If the user expects that a column has a NULL value and the user wishes to avoid the ambiguity of an undefined result, then one of these methods can be used in an expression to convert a NULL as desired.
In an un-named complex expression (that is, an expression defined as “begin … end;”), you can explicitly check for NULL and handle NULL values as desired. For example:
begin returns int;
if (<column> == NULL)
return 10; /*do something special to handle NULL */
else
return <column> + 10; /* if column value is not NULL */
end;Note that since columns cannot be directly referenced in a user procedure (that is, named complex expressions) and a reference to the NULL literal is not allowed, there is no way to check for NULL values in a procedure. NULL values have to cleansed before calling a user procedure; otherwise, an undefined exception can occur. In the following example, assume that <column> is “nulls allowed”:
/* this is a user procedure */
procedure testproc (int input) returns boolean;
if (input > 10)
return true;
else
return false;
end;
/* An expression that calls it */
/* This can get an “undefined” exception. */
testproc(<column>);
/* Another expression that calls it */
/* The call to testproc is “safe” */
testproc(nulltoingeter(<column>,0));Note that the warning message associated with a “nulls allowed” source column is suppressed if the column referenced is used within the context of a “nullto” method.