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
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))
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)