DECODE: Decoding Values

How to:

Reference:

Available Languages: reporting, Maintain

The DECODE function assigns values based on the coded value of an input field. DECODE is useful for giving a more meaningful value to a coded value in a field. For example, the field GENDER may have the code F for female employees and M for male employees for efficient storage (for example, one character instead of six for female). DECODE expands (decodes) these values to ensure correct interpretation on a report.

You can use DECODE by supplying values directly in the function or by reading values from a separate file.

The use of DECODE with Maintain is limited. For information on decoding values with subscripted stack values, see SELECTS: Decoding a Value From a Stack.


Top of page

x
Syntax: How to Supply Values in the Function
DECODE fieldname(code1 result1 code2 result2...[ELSE default ]);
DECODE fieldname(filename ...[ELSE default]);

where:

fieldname

Alphanumeric or Numeric

Is the name of the input field.

code

Alphanumeric or Numeric

Is the coded value that DECODE compares with the current value of fieldname. If the value has embedded blanks, commas, or other special characters, it must be enclosed in single quotation marks. When DECODE finds the specified value, it returns the corresponding result. When the code is compared to the value of the field name, the code and field name must be in the same format.

result

Alphanumeric or Numeric

Is the returned value that corresponds to the code. If the result has embedded blanks or commas, or contains a negative number, it must be enclosed in single quotation marks. Do not use double quotation marks (").

If the result is presented in alphanumeric format, it must be a non-null, non-blank string. The format of the result must correspond to the datatype of the expression.

default

Alphanumeric or Numeric

Is the value returned as a result for non-matching codes. The format must be the same as the format of result. If you omit a default value, DECODE assigns a blank or zero to non-matching codes.

filename

Alphanumeric

Is the name of the file in which code/result pairs are stored. Every record in the file must contain a pair.

You can use up to 40 lines to define the code and result pairs for any given DECODE function, or 39 lines if you also use an ELSE phrase. Use either a comma or blank to separate the code from the result, or one pair from another.

Note: DECODE has no output argument.



Example: Supplying Values Using the DECODE Function

EDIT extracts the first character of the CURR_JOBCODE field, then DECODE returns either ADMINISTRATIVE or DATA PROCESSING depending on the value extracted.

TABLE FILE EMPLOYEE
PRINT CURR_JOBCODE AND COMPUTE
DEPX_CODE/A1 = EDIT(CURR_JOBCODE, '9$$'); NOPRINT AND COMPUTE
JOB_CATEGORY/A15 = DECODE DEPX_CODE(A 'ADMINISTRATIVE' 
B 'DATA PROCESSING') ;
BY LAST_NAME
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

LAST_NAME    CURR_JOBCODE    JOB_CATEGORY
---------    ------------    ------------
BLACKWOOD    B04             DATA PROCESSING
CROSS        A17             ADMINISTRATIVE
GREENSPAN    A07             ADMINISTRATIVE
JONES        B03             DATA PROCESSING
MCCOY        B02             DATA PROCESSING
SMITH        B14             DATA PROCESSING

Top of page

x
Reference: Guidelines for Reading Values From a File


Example: Reading DECODE Values From a File

The following example has two parts. The first part creates a file with a list of IDs and reads the EDUCFILE data source. The second part reads the EMPLOYEE data source and assigns 0 to those employees who have taken classes and 1 to those employees who have not. The HOLD file contains only one column of values; therefore, DECODE assigns the value 0 to an employee whose EMP_ID appears in the file and 1 when EMP_ID does not appear in the file.

TABLE FILE EDUCFILE
PRINT EMP_ID
ON TABLE HOLD
END
TABLE FILE EMPLOYEE
PRINT EMP_ID AND LAST_NAME AND FIRST_NAME AND COMPUTE 
NOT_IN_LIST/I1 = DECODE EMP_ID(HOLD ELSE 1); 
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

EMP_ID     LAST_NAME        FIRST_NAME  NOT_IN_LIST
------     ---------        ----------  -----------
112847612  SMITH            MARY                  0
117593129  JONES            DIANE                 0
219984371  MCCOY            JOHN                  1
326179357  BLACKWOOD        ROSEMARIE             0
543729165  GREENSPAN        MARY                  1
818692173  CROSS            BARBARA               0

WebFOCUS