Easytrieve LOOKUP — Table Search and Reference Data

SEARCH — The Lookup Statement

1
SEARCH file-name WITH search-field GIVING result-field

File-name is the TABLE file from Library. Search-field holds the key to find; result-field receives DESC data when the search succeeds. Immediately follow with IF file-name or IF NOT file-name to branch on match versus no match.

Define a TABLE File

1
2
3
4
5
6
7
8
9
10
DEFINE CODE         W  4 A
DEFINE DESCRIPTION  W 40 A
FILE CLASSES TABLE INSTREAM
  ARG   1  4 A
  DESC 10 40 A
1011     ENGLISH I
1012     ENGLISH II
1013     ENGLISH III
1014     ENGLISH IV
ENDTABLE

ARG defines where the key sits in each table row; DESC defines the returned text. Instream rows follow the same record layout. Table entries must be in ascending ARG order without duplicates for binary search. Alphanumeric ARG maximum length is 54 bytes per Table Processing chapter.

INSTREAM Versus External Tables

INSTREAM tables are established at compile time and limited by available memory. External tables load when the JOB containing SEARCH starts. If entry count exceeds installation default, specify max-table-entries on FILE TABLE. External files must be sequentially accessible fixed-length records matching ARG/DESC layout.

JOB Example With Zip Code Lookup

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
FILE PERSNL FB(150 1800)
  EMPNAME  6 20 A
  STATE   69  2 A
  ZIP     71  5 N
  GROSS   94  7 P 2
 
DEFINE POST-OFFICE-DESC W 20 A
 
FILE ZIPTABLE TABLE 5000
  ARG   1  5 N
  DESC  7 20 A
 
JOB INPUT PERSNL NAME STATE-RPT
  IF STATE = 'DC' 'IL'
     SEARCH ZIPTABLE WITH ZIP GIVING POST-OFFICE-DESC
     IF NOT ZIPTABLE
        POST-OFFICE-DESC = 'BAD ZIP CODE'
     END-IF
     PRINT STATE-RPT
  END-IF
 
REPORT STATE-RPT
  SEQUENCE STATE
  CONTROL STATE
  TITLE 01 'EMPLOYEE SALARIES BY STATE'
  LINE 01 STATE EMPNAME GROSS ZIP POST-OFFICE-DESC

Broadcom JOB Activities example decodes ZIP to post office description for selected states. Failed search sets a literal message when IF NOT ZIPTABLE is true. LINE prints the decoded field alongside employee data.

Class Table PROGRAM Example

1
2
3
4
5
6
7
8
PROGRAM NAME MYPROG
  MOVE '1012' TO CODE
  SEARCH CLASSES WITH CODE GIVING DESCRIPTION
  IF CLASSES
     DISPLAY DESCRIPTION
  ELSE
     DISPLAY 'CLASS NOT FOUND'
  END-IF

Working-storage CODE holds the search key. Successful IF CLASSES means DESCRIPTION contains ENGLISH II for key 1012. DISPLAY writes to SYSPRINT for quick tests before embedding SEARCH in production JOB logic.

Field Matching Rules

WITH search-field length and type must match ARG on the TABLE file. GIVING result-field must match DESC. Search-field cannot be varying length or nullable. Logical compare treats both sides as alphanumeric for the search operation per Language Reference even when types differ in DEFINE—still match declared lengths to avoid subtle mismatches. Result-field cannot be K field or TABLE file internal field.

Where You Can Code SEARCH

SEARCH is valid in PROGRAM, SCREEN, and JOB activities. Multiple SEARCH statements against multiple tables are allowed per record or screen cycle. Order matters only for business logic— each SEARCH is independent. Do not SEARCH a file without TABLE parameter; ordinary FILE input uses GET or automatic JOB INPUT instead.

Lookup Error Handling Patterns

• Default description literal when IF NOT table-file.
• GOTO JOB to skip record with invalid code.
• PRINT exception report listing key and input record key fields.
• Increment error counter W field for summary TITLE on audit report.
• DISPLAY during development; remove or gate for production volume.

LOOKUP Versus SQL and ACCESS

TABLE lookup is fast for hundreds or thousands of static rows with fixed layout. SQL FILE definitions suit large shared tables maintained in Db2. ACCESS supports indexed or relational verbs per Integrating chapter. Maintenance teams often keep department and status decodes in INSTREAM TABLE for batch reports while HR master stays in Db2 accessed occasionally via SQL FILE in the same program.

Table Maintenance Discipline

External tables must be regenerated when codes change. Instream tables require recompile. Version control both program and external table dataset. Audit ARG sort order after edits—one out-of-order row breaks binary search silently producing NOT FOUND for valid keys. Duplicate ARG values make match behavior undefined; deduplicate source data.

Performance Notes

Binary search is efficient per lookup. Thousands of SEARCH calls per input record across many tables still add CPU—consolidate tables where possible. Loading large external TABLE at JOB start consumes memory; size max-entries realistically. JOB INPUT NAME TABLE-SEARCH on JOB line documents table use for operators reading listings.

Common Lookup Mistakes

• Searching FILE without TABLE parameter.
• Unsorted or duplicate ARG values in instream data.
• Mismatched WITH field length versus ARG.
• Using GIVING result without IF presence test.
• Expecting LOOKUP keyword instead of SEARCH.
• Nullable or varying length search fields.

Explain It Like I'm Five

Lookup is using a dictionary. You have a word—the department number—and you look in the dictionary table to find the full description. SEARCH is opening the right page. IF ZIPTABLE means the dictionary had the word; IF NOT ZIPTABLE means you spell it wrong and use a default message. The TABLE is the dictionary book you built in Library with keys on the left and meanings on the right.

Exercises

1. Build INSTREAM CLASS table with four ARG/DESC rows and PROGRAM SEARCH test.
2. Add IF NOT handler with literal default description.
3. List three differences between INSTREAM and external TABLE.
4. Extend zip example to PRINT only when lookup succeeds.
5. Document when SQL FILE replaces TABLE for your shop data.