decode(X;V1 V2 ...;R1 R2 ... RD)

Returns the value from the second list that corresponds to the particular element a given value matches in the first list; if there are no matches, returns the specified default value.

Syntax

decode(X;V1 V2 ...;R1 R2 ... RD)

Input

Argument Type Description
X any simple type A scalar value or the name of a column
V any simple type The values specified for the elements in V must be the same type as X.

A space- or comma-separated list of values
R any simple type The list R should have one more element (RD) than V, which is the default return value.

The values specified for all of the elements in R must be the same type (integer, decimal, or text).

If RD is not specified (i.e., if R has the same number of elements as V), the default return value is N/A.

A space- or comma-separated list of values

Return Value

Returns a numeric or text value (depending on the type of the elements specified for R) corresponding to the following:
  • If X is V1, return R1; else if X is V2, return R2; and so on.
  • If X does not match any of the elements in V, return RD. If RD is not specified, return N/A.

Sample Usage

X V R decode(X;V;R)
3 1 2 3 4 5 'one','two','three','four','five','who knows' 'three'
0 1 2 3 4 5 'one','two','three','four','five','who knows' 'who knows'
0 1 2 3 4 5 'one','two','three','four','five' NA

Example

Let's use the table pub.demo.retail.item to illustrate the use of decode(X;V1 V2 ...;R1 R2 ... RD).

Let's say we have three stores and that store 1 is located in New York, store 2 is located in Miami, and store 3 is in London. To add a column to our table that corresponds to the location for each store, we can use the following Macro Language code:
<willbe name="location" value="decode(store;1 2 3;'New York','Miami','London','Not a valid location')"/>

This will give us the following new column:

Since, in our example, there are no values in the store column that are not 1, 2, or 3, the default value ('Not a valid location') is never used.