An expression is considered valid if it is syntactically correct and all function, operator and column references can be resolved. If an expression is not valid, it cannot be evaluated. This will be indicated in the Sample result field as "#Error", or similar.
When evaluating an expression, some operations produce errors or null values. Each function and operator can choose how to handle arguments that are errors or null. The default behavior is to propagate the first error or null value that is encountered. So, if a function takes two arguments and the first is null and the second is an error, the default behavior is to return null. Functions that handle errors and null in other ways are explained in their Descriptions.
Errors
For operations on arguments of type real, the result is sometimes not defined. This produces a #NA (Not Applicable) error. For example, this could happen if the result of an operation is greater than the maximum real number.
If the argument for a function (or operator) is invalid, an error is also produced. For example, functions expecting an index might produce an error because the index is negative and conversion functions produce an error if the argument cannot be converted.
The SE(Arg1, Arg2) function can be used to substitute errors with a certain value, for example null or 0.
When generating a result data table from the expression, errors are converted to null. Wrap the expression with a call to the SN(Arg1, Arg2) function to override this behavior.
|
Error |
Example |
Description |
|
NA |
0/0 sqrt(-1) |
NA, Not Applicable, means that the result is undefined. |
|
Negative Argument |
Left("Hello", -1) Mid("Hello", 0, 1) |
Invalid argument. In this case because length must be positive and position cannot be zero. |
|
(Empty) |
Real("Hello") |
Conversion failed. |
(Empty)
Empty values are usually only generated when a column value from the data table is missing or by explicitly writing null in the expression. Results that are null, are displayed as "(Empty)".
The SN(Arg1, Arg2) function can be used to substitute null with a certain value, for example, 0.