Lookup Function

The Lookup Function obtains the value for a destination column from a lookup table, according to the value in a source column. There are two forms of the Lookup Function, single column and multiple column.

The single column form inserts a value into a single destination column. The multiple column form inserts values from multiple lookup table columns into corresponding destination columns.

You can enter the multiple column Lookup Function for any source column that will be replaced by a lookup table value, but you must edit the Column Map to remove the names of remaining source columns that will also be replaced.

The IGNORE parameter allows you to ignore the lookup table and use a source value when a row in a specified source column contains a specified value (NULL, SPACES (for CHAR columns), or zero-length VARCHAR).

You can use the PRESERVE parameter to ignore the lookup table and use a source value when a row in a specified source column contains a specified value (NULL, SPACES (for CHAR columns), or zero-length VARCHAR). PRESERVE can also be used to ignore the lookup table if a source column does not contain a value.

If a match is not found in the lookup table, a conversion error is reported.

The syntax is:

LOOKUP ( [sourcecol,] [dest=(col1, coln) , ]
lktablename (search, {value | values=(col1, coln) }
[,cache | ,nocache ] )

[,ignore=(colname ( spaces, null, zero_len ), )
| PRESERVE=( [ NOT_FOUND, ] colname (spaces, null, zero_len), ) ] )
sourcecol
Name of the source table column that contains the search value (optional). If not specified, the name of the destination column is used.
dest=
Names of the destination table columns in which values from the lookup table are inserted. (Required for multiple column lookup.)
col1, coln
Destination table column names. The order of the column names must correspond to the lookup table columns in the values= parameter.
lktablename
Name of the lookup table. You may specify the lookup table name as dbalias.creatorid.tablename, creatorid.tablename, or tablename. If you do not fully qualify the table name, the qualifiers for the destination table are used.
search
Name of the column in the lookup table that contains a value to match against the search value from the source column.
value
Name of the column in the lookup table that contains the translated search value to be inserted at the destination. (Required for single column lookup.)
values=
Names of the lookup table columns that contain values to be inserted at the destination. (Required for multiple column lookup.)
col1, coln
Lookup table column names. The order of the column names must correspond to the destination table columns in the dest= parameter.
cache | nocache
Specify cache (default) to maintain a table of found lookup values in memory or nocache to discard found values. Using cache is faster when retrieving a value many times, but requires extra memory.
ignore=
List of source columns with values that are inserted at the destination instead of the lookup value when the column has a row with a stated value (null, spaces, zero or zero-length varchar).
col
The source column name.

For single column lookup, enter one column name only.

For multiple column lookup, the order of the column names must correspond to the destination table columns in the dest= parameter. The number of columns must equal the columns in the dest= parameter, and at least one column must include values. To not specify values for a column, do not enter a value. For example, coln().

null
Ignore the lookup table if the source column row has a null value.
SPACES
Ignore the lookup table if the source column row has a SPACES value. For CHAR columns only.
ZERO_LEN
Ignore the lookup table if the source column row has a zero-length VARCHAR value.
PRESERVE=
List of source columns with values that are inserted at the destination instead of the lookup value when the source column contains a stated value (NOT_FOUND, null, spaces, or zero-length varchar).
NOT_FOUND
Ignore the lookup table if no match is found for the source column row.
Note:

Preserve= and ignore= are mutually exclusive. Ignore= will be deprecated in a future release.

The col, null, spaces, and zero-length varchar operands have the same effect when used with either preserve= or ignore=. Details on these operands are shown above with ignore=.

Single Column Example

Use the Lookup Function to translate the source value in a lookup table to a corresponding value in another table.

For example, assume the source column, STATE, contains state abbreviations (for example, NJ) and the destination column is to contain the complete state name (in this example, New Jersey). A lookup table named STATE_LOOKUP contains a column (CODE) for state abbreviations or codes and a column (NAME) for the corresponding names.

To obtain the value for the destination column using the STATE_LOOKUP table, specify:

LOOKUP(STATE,STATE_LOOKUP(CODE,NAME))

The Lookup Function searches for a value in the CODE column of the STATE_LOOKUP table that matches the value (NJ) in the source table STATE column. When a match is found, the function inserts the corresponding value from the NAME column (New Jersey) in the destination column.

Multiple Column Example

Use the Lookup Function to insert values from columns in a lookup table row into columns in a destination table row, based on a value in a source column.

For example, based on a source column (SOC_SEC) that contains social security numbers, you can replace values in destination columns (FIRST_NAME and LAST_NAME) with first and last names from a lookup table. A table named NAME_LOOKUP contains a column (SSN) with the social security numbers from the source table as well as columns (FIRST_MASK and LAST_MASK) to mask corresponding names in the destination.

To replace names in the destination table based on a social security number, specify:

LOOKUP(SOC_SEC,DEST=(FIRST_NAME,LAST_NAME),
NAME_LOOKUP(SSN,VALUES=(FIRST_MASK, LAST_MASK)))

The Lookup Function searches for a value in the SSN column of the NAME_LOOKUP table that matches the value in the source table SOC_SEC column. When a match is found, the function inserts the corresponding values from the lookup table FIRST_MASK and LAST_MASK columns into the corresponding destination columns.

Ignore Example

Use the following statement to extend the Single Column Example, where you want to use the source NULL and SPACES values instead of lookup table values:

LOOKUP(STATE,STATE_LOOKUP(CODE,NAME),
IGNORE=(STATE(NULL,SPACES)))

NoCache Example

Use the following statement to extend the Single Column Example, where you do not want to maintain a table of found lookup values in memory:

LOOKUP(STATE,STATE_LOOKUP(CODE,NAME),NOCACHE)