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