Decoding the Characters in an Alphanumeric Field

You can also decode or translate characters stored in a field. For example, job codes could be decoded into job titles.

You can use the DECODE command to do this. DECODE enables you to replace values retrieved from a field with its decoded value.

The basic format of the DECODE command is:

field/format= DECODE fieldname (value result value result...
ELSE default);

In this structure, field is the name you give the temporary field. You supply the format, which must match the format of the results. For instance, if you supply an alphanumeric format for the field, the results of the DECODE must also be alphanumeric. Likewise, if you supply a numeric format, the results must be numeric also.

The fieldname is the name of the field in the data source you want to translate, and value is a data value found in the data source that you specify. The result represents the value you want written to the temporary field when its matching value is found in the data source. The result does not have to be unique. Several values retrieved from the data source can all be translated into the same result.

The keyword ELSE is optional and goes into effect if no values in the data source match the values you list. It is good coding practice to always include an ELSE value. default represents a value you specify to be written to the temporary field. If you do not supply a default value, the previous value is maintained.

In the next example, you use DECODE to translate deduction codes into one of three (3) categories: BENEFITS, SAVINGS, or TAXES. The deduction codes are found in the field DED_CODE. You use a temporary field named D_TYPE for the translated values.

Issue the following DEFINE command:

DEFINE FILE EMPLOYEE ADD
D_TYPE/A10=DECODE DED_CODE (HLTH BENEFITS LIFE BENEFITS
SAVE SAVINGS ELSE TAXES);
END

DED_CODE contains seven (7) possible values: FICA, FED, STAT, CITY, HLTH, LIFE, and SAVE. If the value retrieved from DED_CODE is either HLTH or LIFE, D_TYPE will contain the value BENEFITS. If the value is SAVE, D_TYPE will contain the value SAVINGS. Since the remaining values can only be one of the tax deductions, the default for D_TYPE is TAXES.

Notice also that the expression for D_TYPE spans two lines. The expression can be written on more than one line. A semicolon (;) ends the expression.

Issue the following request:

TABLE FILE EMPLOYEE
SUM DED_AMT AS 'TOTAL,DEDUCTIONS'
BY D_TYPE AS 'DEDUCTION,TYPE'
END

Run the request. The output is: