Lookup

Lookup obtains the value for a destination column from a lookup table, according to the value in a source column. If a match is not found in the lookup table, a conversion error is reported.

There are two forms of lookup, single column search and multiple column search. The single column form searches one source column for a user-specified value. If the value is found in the source column, a corresponding value from the lookup table is inserted into the destination column. In the multiple column form, you specify multiple source columns to search. If the values you specify are found in the source columns, a value from the lookup table is inserted into the destination column. With either single or multiple column lookup, you can also specify multiple destination columns.

The syntax is:

LOOKUP ( { sourcesearchcol, | SRCSEARCH=(sourcecol1,...,sourcecoln), }
            [ dest=(col1,...,coln) , ]
            lktablename ( { lktablesearchcol
            | LKPSEARCH=(lktablesearchcol1,...,lktablesearchcoln) },
            { valuecolname | values=(col1,...,coln) } [ ,cache | ,nocache ] )
            | [ ,PRESERVE=( [ NOT_FOUND, ] colname (spaces, null, zero_len),... ) ]) )
sourcesearchcol
For a single column search, the name of the source column that contains the search value. If not specified, the name of the destination column is used.
SRCSEARCH=
For a multiple column search, the names of the source columns that contain the search values. The list of column names must be enclosed in parentheses with names separated by commas. Specify a maximum of 16 columns to search. If you use SRCSEARCH= , you must also specify LKPSEARCH=.
dest=
Names of the destination table columns in which values from the lookup table are inserted. (Required for multiple column lookup.)
col1, . . . ,coln
List of destination table columns, separated by commas and enclosed in parentheses. The order of names must correspond to the order of lookup table column names in values=.
lktablename
Name of the lookup table. 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.
lktablesearchcol
For single column lookup, name of the column in the lookup table that contains a value to match against the search value from the source column.
LKPSEARCH=
For multiple column lookup, specify the columns in the lookup table that contain values to match against the search values from the source columns. Specify the same number of columns for LKPSEARCH= as for SRCSEARCH=.
lktablesearchcol1, . . . ,lktablesearchcoln
Names of the columns in the lookup table with values to match against the search values from the source columns. Column names must be enclosed in parentheses and separated by commas.
valuecolname
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=
For a multiple column lookup, the names of lookup table columns that contain values to be inserted at the destination.
col1, . . . ,coln
List of lookup table columns, separated by commas and enclosed in parentheses. The order of the column names must correspond to the order of destination table column names in dest=.
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 more memory than nocache.
PRESERVE=
Values inserted at the destination if the lookup value is not found or the source contains null or spaces or is a zero-length varchar.
NOT_FOUND
Insert the source value in the destination, if no match is found for the source column row.
colname
A column for which specified source values are inserted at the destination. The source values are:
NULL
SPACES
ZERO-LENGTH VARCHAR

Single column example

Use lookup to translate the source value to a value in a lookup table, which is used to provide a corresponding value in another table.

For example, assume the source column, STATE, contains state abbreviations (for example, NJ). 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))

Lookup 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, lookup inserts the corresponding value from the NAME column (New Jersey) in the destination column.

Multiple column example

In this example, lookup searches the source columns for employee number, national ID, first name, and phone number. Values from the lookup table columns for last name and email ID are inserted into the destination columns.

LOOKUP (SRCSEARCH=(EMPNO,NATIONAL_ID,NAME_FIRST,PHONE_NUMBER),
DEST=(NAME_LAST,EMAILID),
LOOKUP_PERSON(LKPSEARCH=(EMPNO_LKP,NATIONAL_ID_LKP,NAME_FIRST_LKP,PHONE_NUMBER_LKP),
VALUES=(NAME_LAST_LKP,EMAILID_LKP)))

Preserve example

The following statement extends the single column example to use source NULL and SPACES values instead of lookup table values:

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

Nocache example

This statement extends the single column example to avoid maintaining a table of found lookup values in memory:

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



Feedback