LOOKUP: Retrieving a Value From a Cross-referenced Data Source

In this section:

How to:

Available Languages: MODIFY

The LOOKUP function retrieves a data value from a cross-referenced FOCUS data source in a MODIFY request. You can retrieve data from a data source cross-referenced statically in a Master File or a data source joined dynamically to another by the JOIN command. LOOKUP retrieves a value, but does not activate the field. LOOKUP is required because a MODIFY request, unlike a TABLE request, cannot read cross-referenced data sources freely.

LOOKUP allows a request to use the retrieved data in a computation or message, but it does not allow you to modify a cross-referenced data source.

To modify more than one data source in one request, use the COMBINE command or the Maintain Data facility.

LOOKUP can read a cross-referenced segment that is linked directly to a segment in the host data source (the host segment). This means that the cross-referenced segment must have a segment type of KU, KM, DKU, or DKM (but not KL or KLU) or must contain the cross-referenced field specified by the JOIN command. Because LOOKUP retrieves a single cross-referenced value, it is best used with unique cross-referenced segments.

The cross-referenced segment contains two fields used by LOOKUP:

When using LOOKUP, the MODIFY request reads a transaction value for the host field. It then searches the cross-referenced segment for an instance containing this value in the cross-referenced field:

Syntax: How to Retrieve a Value From a Cross-referenced Data Source

LOOKUP(field);

where:

field

Is the name of the field to retrieve in the cross-referenced file. If the field name also exists in the host data source, you must qualify it here. Do not include a space between LOOKUP and the left parenthesis.

Note: LOOKUP does not use an output argument.

Example: Using a Value in a Host Segment to Search a Data Source

You can use a field value in a host segment instance to search a cross-referenced segment. Do the following:

  • In the MATCH command that selects the host segment instance, activate the host field with the ACTIVATE command.
  • In the same MATCH command, code LOOKUP after the ACTIVATE command.

This request displays the employee ID, date of salary increase, employee name, and the employee position after the raise was granted:

  • The employee ID and name (EMP_ID) are in the root segment.
  • The date of increase (DAT_INC) is in the descendant host segment.
  • The job position is in the cross-referenced segment.
  • The shared field is JOBCODE. You never enter a job code; the values are stored in the data source.

The request is:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID DAT_INC
MATCH EMP_ID
   ON NOMATCH REJECT
   ON MATCH CONTINUE
MATCH DAT_INC
   ON NOMATCH REJECT
   ON MATCH ACTIVATE JOBCODE
   ON MATCH COMPUTE
      RTN = LOOKUP(JOB_DESC);
   ON MATCH TYPE
      "EMPLOYEE ID:        <EMP_ID"
      "DATE INCREASE:      <DAT_INC"
      "NAME:               <D.FIRST_NAME <D.LAST_NAME"
      "POSITION:           <JOB_DESC"
DATA

A sample execution is:

  1. The request prompts you for the employee ID and date of pay increase. Enter the employee ID 071382660 and the date 820101 (January 1, 1982).
  2. The request locates the instance containing the ID 071382660, then locates the child instance containing the date of increase 820101.
  3. This child instance contains the job code A07. The ACTIVATE command makes this value available to LOOKUP.
  4. LOOKUP locates the job code A07 in the cross-referenced segment. It returns a 1 the RTN variable and retrieves the corresponding job description SECRETARY.
  5. The TYPE command displays the values:
    EMPLOYEE ID:          071382660
    DATE INCREASE:        82/01/01
    NAME:                 ALFRED STEVENS
    POSITION:             SECRETARY

Fields retrieved by LOOKUP do not require the D. prefix. FOCUS treats the field values as transaction values.

You may also need to activate the host field if you are using LOOKUP within a NEXT command. This request displays the latest position held by an employee:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
MATCH EMP_ID
   ON NOMATCH REJECT
   ON MATCH CONTINUE
NEXT DAT_INC
   ON NONEXT REJECT
   ON NEXT ACTIVATE JOBCODE
   ON NEXT COMPUTE
      RTN = LOOKUP(JOB_DESC);
   ON MATCH TYPE
      "EMPLOYEE ID:      <EMP_ID"
      "DATE OF POSITION: <DAT_INC"
      "NAME:             <D.FIRST_NAME <D.LAST_NAME"
      "POSITION:         <JOB_DESC"
DATA

Example: Using the LOOKUP Function With a VALIDATE Command

When you use LOOKUP, reject transactions containing values for which there is no corresponding instance in the cross-reference segment. To do this, place the function in a VALIDATE command. If the function cannot locate the instance in the cross-referenced segment, it sets the value of the return variable to 0, causing the request to reject the transaction.

The following request updates an employee's classroom hours (ED_HRS). If the employee enrolled in classes on or after January 1, 1982, the request increases the number of classroom hours by 10%. The enrollment dates are stored in a cross-referenced segment (field DATE_ATTEND). The shared field is the employee ID.

The request is as follows:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID ED_HRS
VALIDATE
   TEST_DATE = LOOKUP(DATE_ENROLL);
COMPUTE
   ED_HRS = IF DATE_ENROLL GE 820101 THEN ED_HRS * 1.1
         ELSE ED_HRS;
MATCH EMP_ID
   ON MATCH UPDATE ED_HRS 
    ON NOMATCH REJECT
DATA

If an employee record is not found in the cross-referenced segment, that employee never enrolled in a class. The transaction is rejected as an error.

Using the Extended LOOKUP Function

How to:

If the LOOKUP function cannot locate a value of the host field in the cross-referenced segment, use extended syntax to locate the next highest or lowest cross-referenced field value in the cross-referenced segment.

To use this feature, create the index with the INDEX parameter set to NEW (the binary tree scheme). To determine the type of index used by a data source, enter the FDT command.

Syntax: How to Use the Extended LOOKUP Function

COMPUTE
LOOKUP(field action);

where:

field

Is the name of the field in the cross-referenced data source, used in a MODIFY computation. If the field name also exists in the host data source, you must qualify it here.

action

Specifies the action the request takes. Valid values are:

EQ causes LOOKUP to take no further action if an exact match is not found. If a match is found, the value of rcode is set to 1; otherwise, it is set to 0. This is the default.

GE causes LOOKUP to locate the instance with the next highest value of the cross-referenced field. The value of rcode is set to 2.

LE causes LOOKUP to locate the instance with the next lowest value of the cross-referenced field. The value of rcode is set to -2.

Do not include a space between LOOKUP and the left parenthesis.

The following table shows the value of rcode, depending on which instance LOOKUP locates:

Value

Action

1

Exact cross-referenced value located.

2

Next highest cross-referenced value located.

-2

Next lowest cross-referenced value located.

0

Cross-referenced value not located.