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 OR 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 ENDINF will 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.