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.
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), ) ] )
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().
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=.
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.
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.