@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 asNULL
. -
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 theHIGH_SALARY
column only if theBASE_SALARY
column in the source record was both present (and notNULL
) 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 forAMT
is returned.AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)