Testing functions
Overview
Testing functions are useful for decision-making and filtering. For example, if LONGITUDE is a number, you can build expressions like If IsValid(LONGITUDE) Then LONGITUDE Else 0 Endif which substitutes 0 (zero) for invalid numbers.
Testing functions are also useful in Filter tool expressions. You can separate records with valid values from other records, and perform different processing on each one.
Null and error values
Two kinds of "non-values" are used to store empty values, unknown values, and the results of improper computations: Null and Error.
Null is the all-purpose designator for "empty" or "unknown" values. All data types in Data Management support a value of Null. A Null value can result when:
Blank or Null data is converted from input sources,
a string operation produces an empty string, or
an empty string is converted to another type.
You can give a field a Null value by assigning the literal Null. However, you cannot use Null as part of a larger expression, except as the THEN or ELSE expression of a conditional.
When a Null value is used in an expression, the result is Null. This is known as "Null propagation." (But see Exceptions to Null and Error propagation.)
Error is the all-purpose "error" designator. All data types in Data Management support a value of Error. Error results from improper operations, such as:
Division by zero
Arithmetic overflow
Arithmetic conversions that do not fit in the target field
Failed type conversions, such as trying to interpret the string "BLUE" as a Date.
Putting a large string in a smaller field does not produce an Error value; it simply truncates the string (possibly generating a warning message in the report).
When an Error value is used in an expression, the result is Error. This is known as "Error propagation." (But see Exceptions to Null and Error propagation.)
Exceptions to null and error propagation
A Null string is processed as if it were an empty string. For example, adding "X" to a Null string produces "X".
A fixed-length text field can contain the value Null, which is different than a field full of blanks.
The function IsNull returns True when the argument is a Null value, and False otherwise. It never returns a Null or Error value, even when the argument is an Error or Null value.
The function IsError returns True when the argument is an Error value, and False otherwise. It never returns a Null or Error value, even when the argument is an Error or Null value.
A conditional expression of the form
IF condition THEN thenExpression ELSE elseExpression ENDINFwill completely ignore elseExpression if the condition is True (even if elseExpression is Null or Error). Likewise, it will ignore thenExpression when the condition is False.If the condition expression in an IF/THEN/ELSE/ENDIF expression is Null or Error, it is treated as False.
If either the left-hand or right-hand sub-expression of a logical AND expression is Null or Error, the entire expression evaluates to False.
If both the left-hand and right-hand sub-expression of a logical OR expression is Null or Error, the entire expression evaluates to False.
If a Filter expression evaluates to Null or Error, it is treated as False.
AnyNull
Returns True if any field in the record is a null value (or empty string). AnyNull is useful in a Filter tool to separate Null values from valid ones. It is also useful in conditional expressions, to replace Null values with something else.
Syntax
AnyNull( )
AnyNull has no arguments.
Remarks
Reasons that AnyNull might return True include:
Null value converted from an SQL database.
Blank value converted from an ASCII file.
Example
NullCheck = AnyNull()
AnyError
Returns a Boolean value, which is True if any field in the record is an Error value, otherwise False. Note that Null is not considered an Error value.
Syntax
AnyError( )
AnyError has no arguments.
Remarks
Reasons that AnyError might return True include:
Malformed field value (i.e., a flat-file field value was scanned but is of incorrect format).
Numeric overflow.
Divide-by-zero error.
Result of an expression containing one or more invalid values.
Example
IF AnyError() THEN "Error" ELSE "OK" ENDIF
Error
Stops project execution and displays the specified error message.
Syntax
Error( message )
The required argument message must be of type Text.
Remarks and examples
Use the Error function to stop a project when logical error conditions are detected in the data. We recommend using it in one of two patterns.
Use it to detect errant records where no records should be produced:
Attach a Calculate tool to the data stream which is supposed to be empty, and configure the Calculate tool to assign an error statement to any variable:Error("Error in record data detected!").
You may find this technique useful in the following situations:
You want to verify that all records meet a condition, and cause processing to halt when this condition is not met.
You are joining two data streams, and expect the records to join completely.
Use it in the middle of an IF/THEN/ELSE/ENDIF statement to halt processing when unexpected data is detected in mid-calculation. For example, to convert ranges of numbers into single-letter codes, you might use an expression like
IF VALUE < 1000 THEN "A" ELSEIF VALUE < 2000 THEN "B" ELSE Error("Value " + AsText(VALUE) + " is out of range") ENDIF.
IsError
Returns a Boolean value, which is True if the argument is an Error value, otherwise False. Note that Null is not considered an Error value.
Syntax
IsError( value )
The required argument value may be of any type.
Remarks
Reasons that IsError might return True include:
Malformed field value (for example, a flat-file field value was scanned but is of incorrect format).
Numeric overflow.
Divide-by-zero error.
Result of an expression containing one or more invalid values.
Example
IF IsError(ReturnValue) THEN 0 ELSE ReturnValue ENDIF
IsNull
Returns True if the argument is Null, False if the value is not Null. IsNull is useful in a Filter tool to separate Null values from valid ones. It is also useful in conditional expressions, to replace Null values with something else like IF IsNull(DATE) THEN MakeDate(1, 1, 1600) ELSE DATE ENDIF.
Syntax
IsNull( value )
The required argument value may be of any type.
Remarks
Reasons that IsNull might return True include:
Null value converted from an SQL database.
Blank value converted from an ASCII file.
Example
NullCheck = IsNull(Field1)
IsOneOf
Returns a Boolean value, which is True if any matches any of testvalue1 .. testValueN, otherwise False.
Syntax
IsOneOf( any, testvalue1, testvalue2, ...)
The required argument anyvalue may be any value or expression. The argument testvalueN may be any constant value or expression.
Remarks
Checks to see if your item exists in a list.
Example
IsOneOf(uppercase(STATE), "AK", "AZ", "AL", ... "WY")
IsValid
Returns a Boolean value, which is True if the argument is a valid value, and False otherwise.
Syntax
IsValid( value )
The required argument value may be of any type.
Remarks
Reasons that IsValid might return False include:
Malformed field value (for example, a flat-file field value was scanned but is of incorrect format).
Null value converted from an SQL database.
Blank value converted from an ASCII file.
Numeric overflow.
Divide-by-zero error.
Result of an expression containing one or more invalid values.
Given a text string, you can test to see if it could be interpreted as some other type by first converting it to the desired type, and then testing validity.
IsValid( value ) is equivalent to NOT IsError( value ) AND NOT IsNull( value ).
Example
If FIELD1 is a Text field, then IsValid(AsInteger(FIELD1)) returns True if the FIELD1 value of the current record could be interpreted as an integer.