LOOKUP

Searches a file or table for a record that contains a specified string in the correct location. Enrichment returns the entire record that contains the matching information. Lookup files can be system files or tables included in the control file by the Table tag group.

If you use Enrichment on an mainframe system, use the LOOKUPV function to look up information in keyed VSAM files. VSAM files are recommended if your data sets are very large and insufficient memory is available for LOOKUP processing.

Note: File buffers are also used for the LOOKUP function to hold loaded tables. If you are doing ALL-AT-A-TIME Processing and storing in memory, there is a chance that file buffers will be full.

You can use the UPDATE function to update the same file used in a LOOKUP function. However, when Enrichment processing is complete, the updated file will be sorted by the LOOKUP string and empty records in the file will be deleted.

You can use the RGET or SUBSTR functions to parse the return from LOOKUP to obtain specific variables.

The following diagram illustrates how the LOOKUP function interacts with the lookup file.

In the example, the key for the lookup file is in columns 1 and 2 (the state). In the rule, a variable (%%State) is set to the key for the desired record (KY). Then, the LOOKUP function is called specifying the file name, key value, and key location. The matching record, if any, is passed back to the variable %%REC. The agency is then extracted from the record using the SUBSTR function.

The key passed to the LOOKUP function must have the same format as the data stored in the file. If the key is stored in binary or packed decimal format, you must pass the key to LOOKUP in the same form. As shown below, you can use the RPUT function to create these formats.

/* Get customer record based on key. Key is in packed decimal format */ 
%%cust_key = RPUT(%%cust_key, %%custnum, L, 1, 6) 
%%cust_record = LOOKUP(DD:FILE1, %%cust_key, 1, 3)

This example shows how to use LOOKUP to Obtain Customer Number From a Flat File:

%%customer_record = LOOKUP(DD:CUSTOMER, %%custnum, 1, 10) 
%%cust_address = SUBSTR(%%customer_record, 1, 40)

This example shows how to use LOOKUP to Obtain Special Processing Information Based on Customer Number From a Flat File:

/* Determine if this customer is to be manually processed*/ 
%%outsort = LOOKUP(“'PROCESS.MANUAL(CUSTOMER)'”, %%custnum, 1, 10) 
IF %%rc = 0 THEN /* If the record is found, then outsort */ 
   <OUTPUT> MANUAL 
ELSE 
   <OUTPUT> AUTOMATE 
ENDIF

Note: Because Enrichment uses a binary search when LOOKUP is called, if a key has multiple matching records in the file or table, you are not guaranteed to get the first matching record, any matching record may be found. To prevent this, the file or table must be modified prior to calling Enrichment so that keys are unique within the file or table.

Syntax

LOOKUP(source,string,start,length,[sorted])

LOOKUP(source,string,nthField,delimiter,[sorted])

Arguments


Argument	Description		Default
source	Either a file name or a table name. For file name, the specification of the data set that contains the records to search. The data set should contain only one record per transaction. You can use the same data set in multiple LOOKUP calls. Specify `source` in exactly the same manner for all calls to the same file. Enrichment only reads the data set once. Alternatively, use TABLE:`tablename` in place of source to specify a table defined in the control file Table tag group.		None
string	The key used to identify a matching record in the data set. Enrichment truncates or pads `string` with blanks (left-justified) to the length defined by `start` and `length`. `string` must duplicate the data in the data set exactly to result in a match. If a record in the data set does not contain enough data to include the `string` size, Enrichment pads the record with blanks to the appropriate size.		None
start	The column position (greater than 0) on which to begin searching for `string` in each record of the data set.		None
length	The total number of columns (greater than 0) that `string` occupies within the data set.		None
nthField	If the length or delimiter argument is a character, the `start` argument will mean field number, rather than column number.		None
delimiter	If `length` is specified as a character it will be a delimiter character used to separate the record into fields.		None
[sorted]	Use one of the following:		N
	Y	The data set has been sorted. Before you set this value to Y, ensure that your data is sorted; otherwise unpredictable results will occur.
	N	The data set has not been sorted. Enrichment will sort it during its initial read.
	For performance, Enrichment performs all searching in memory using a fast binary search algorithm. Enrichment reads the entire data set into memory and sorts it by `string` (if you set `sorted` to N) before processing the first document. Enrichment does not sequentially search the data set. Binary data is not supported. Any record consisting of all blanks is deleted.

Results


Result	Description
Return	The record from `source` that contained `string`.
%%RC	One of the following:
	0	No error.
	1	No matching record found. The return is a null string (' ').
	3	LOOKUP file not loaded. See error message in report file for cause.
	4	If you are using LOOKUP on the same file more than once, start and length values must be the same for each LOOKUP command written.
%%RM	A null string ('').
%%RV	This record number is the position of the record in the table after being sorted by the LOOKUP key.

Examples

A data set contains the following records:

in which columns 1 through 6 indicate a customer number, columns 7 through 9 indicate a branch office ID, and column 10 indicates a special customer status.

Within the rule file for the application, you can use LOOKUP to find records that contain the customer number. Then, Enrichment can use the return from LOOKUP to do the following:

Add a personalized page for special customers.
Outsort the documents based on branch office ID.

To achieve the desired result, rule file syntax might resemble the following:

%%answer=LOOKUP('DD:CUSTINFO',%%custnumber,1,6,N) 
IF %%RC<>0 THEN 
   %%branch=1
   %%special='N' 
ELSE 
   %%branch=SUBSTR(%%answer,7,3) 
   %%special=SUBSTR(%%answer,10,1) 
ENDIF 
IF(%%special='Y') THEN 
   <INPUT>SPECIAL 
ENDIF IF(%%branch>50) 
   <OUTPUT>WEST 
ELSE 
   <OUTPUT>EAST 
ENDIF

Another data set contains the following records:

A12345, John Smith 
B9876, Joe Johnson 
E14679, Amy Anderson 
. 
. 
. 
A4012, Wendy Jackson

The data is not sorted. The data is comma separated and the first field is a key.

To look up a record containing the key “E14679”.

%%custid = "E14679" 
%%record = LOOKUP('DD:CUSTINF2', %%custid, 1, ',', N)

%%record would return the entire record "E14679, Amy Anderson"

1 for the first field, ‘,’ for the delimited, and N for not sorted.