7.10 COLTEST

Use the @COLTEST function to enable conditional calculations by testing for one or more column conditions. If a condition is satisfied, @COLTEST returns TRUE. To perform the conditional calculation, use the @IF function.

Syntax

@COLTEST (source_column, test_condition [, test_condition] [, ...])

source_column

The name of a source column.

test_condition

Valid values:

PRESENT

Indicates a column is present in the source record and not NULL. Column values can be missing if the database does not log values for columns that do not change, but that is not the same as NULL.

NULL

Indicates a column is present in the source record and NULL.

MISSING

Indicates a column is not present in the source record.

INVALID

Indicates a column is present in the source record but contains invalid data.

Examples

Example 1   

The following example uses @IF to map a value to the HIGH_SALARY column only if the BASE_SALARY column in the source record was both present (and not NULL) and greater than 250000. Otherwise, NULL is returned.

HIGH_SALARY =
@IF (@COLTEST (BASE_SALARY, PRESENT) AND
BASE_SALARY > 250000,
BASE_SALARY, @COLSTAT (NULL))

Example 2   

In the following example, 0 is returned when the AMT column is missing or invalid; otherwise a value for AMT is returned.

AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)