Use COLTEST
to perform conditional calculations. COLTEST
can check for one or more of the following column conditions and returns TRUE
if one of the following column conditions are met.
PRESENT
, which indicates a column is present in the source record and not null. In a compressed record, columns may be missing, but this not the same as null.
NULL
, indicating the column is present and NULL
.
MISSING
, indicating that the column is not present.
INVALID
, indicating the column is present but contains invalid data. For example, a PIC 9(3)
field that contains spaces yields an INVALID
condition.
Syntax
@COLTEST (source_field, test_item [, test_item] [, ...])
Examples
This example shows how you can calculate the value of a HIGH_SALARY
column only if the SALARY
field in the source record is both present and greater than a certain number. Set up a test condition with the @IF
function to return the result of SALARY
when part of the current record and exceeding 250000
, otherwise return NULL
:
HIGH_SALARY = @IF(@COLTEST(BASE_SALARY, PRESENT) AND BASE_SALARY > 250000, BASE_SALARY, @COLSTAT(NULL))
In this example, the condition BASE_SALARY > 250000
is evaluated only when SALARY
is present in the source record and not null. If the presence of the column was not tested first, the column would not have been mapped, because the result would have been missing.
In the following example, 0 is returned when AMT
field is missing or invalid, otherwise AMT
is returned.
AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)