Screening Alphanumeric Data

In the previous sections, you selected fields with numeric data. Here, you screen fields that contain alphanumeric data. An alphanumeric field can contain characters, numbers not used in calculations, or a combination of the two.

In this section, you use some relational operators that you used with numeric data, as well as three (3) relational operators used only with alphanumeric data: CONTAINS, OMITS, and LIKE.

As you know, every employee has certain deductions taken from each paycheck. To display the total deductions for each type, sorted by the deduction code, issue the following request:

TABLE FILE EMPLOYEE
SUM DED_AMT
BY DED_CODE AS 'DEDUCTION,TYPE'
END

Run the request. The output is:

All records from the file are retrieved.

Now, suppose you want to see the total amount withheld for Federal, State, and City taxes. Issue the following request:

TABLE FILE EMPLOYEE
SUM DED_AMT
BY DED_CODE AS 'DEDUCTION,TYPE'
WHERE DED_CODE EQ 'FED' OR 'STAT' OR 'CITY';
END

Run the request. The output is:

You have seen that you can screen aggregated values. In this example, you also see that you can screen sort fields (DED_CODE).

Notice that there are now only three (3) deduction types in your report, and that the summed amounts for each of them are the same as those produced in the first report. The screening condition caused only Federal, State, and City deductions to be retrieved.

You can tailor a request to retrieve different types of information about employees. In the following request, you find out which employees have changed job titles within the company. You do this by comparing their current job code to another field called JOBCODE.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME CURR_JOBCODE JOBCODE
BY DAT_INC
WHERE CURR_JOBCODE NE JOBCODE;
END

Run the request. The output is: