DECODE: Decoding Values

How to:

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.

Syntax: How to Supply Values in the Function

DECODE fieldname(code1 result1 code2 result2...[ELSE default ]);
DECODE fieldname(filename ...[ELSE default]);



Alphanumeric or Numeric

Is the name of the input field.


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.


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 data type of the expression.


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.



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

DECODE returns the state abbreviation for PLANT.


For BOS, the result is MA.

For DAL, the result is TX.

For LA, the result is CA.