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 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] [, ...])
source_field

The name of the field or column that is the source of the data being tested.

test_item

One of: PRESENT, MISSING, INVALID, or NULL.

Examples

Example 1   

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.

Example 2   

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)