4.5 COLTEST
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 andNULL
. -
MISSING
, indicating that the column is not present. -
INVALID
, indicating the column is present but contains invalid data. For example, aPIC 9(3)
field that contains spaces yields anINVALID
condition.
Syntax
@COLTEST (source_field
,test_item
[,test_item
] [, ...])
Examples
- Example 1
-
This example shows how you can calculate the value of a
HIGH_SALARY
column only if theSALARY
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 ofSALARY
when part of the current record and exceeding250000
, otherwise returnNULL
: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 whenSALARY
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. - Example 2
-
In the following example, 0 is returned when
AMT
field is missing or invalid, otherwiseAMT
is returned.AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)