Computations: COMPUTE and VALIDATE

Computing Values: The COMPUTE Statement

How to:

Use a COMPUTE Statement

The COMPUTE statement allows you to modify incoming data field values and to define temporary fields.

A transaction data source (whether stored on the computer or typed on paper) used to modify a data source often does not contain the same data that is to go into the data source fields. There are many reasons for this:

The incoming data contains short codes representing the alphanumeric data that is to go into the data source. For example, incoming records contain the code P for PRODUCTION and M for MIS. The PRODUCTION and MIS values update the DEPARTMENT field.
The incoming data is repetitive: the same value is used to update each instance or the same series of values is used to update each segment chain. For example, all employees are to receive a pay increase of 5%.
The incoming data values are calculable from other values. For example, an employee's percentage salary increase is equal to the new salary divided by the old salary minus 1.
Some values vary in predictable ways depending on other values. For example, employee salary increases depend on the employees' department assignment.

The COMPUTE statement gives you control over the data that modifies the data source. Using COMPUTE you can:

Translate codes into data to modify the data source.
Adjust the values of transaction fields.
Define a data value or a series of data values to modify the data source repeatedly.
Calculate data values from other sources and use these new values to modify the data source.

The COMPUTE statement works by setting either an incoming data field or a temporary field to the value of an expression. The expression may involve existing data source fields, other temporary fields, and constants.

Note that there are three different types of fields:

Incoming data fields (also called transaction fields) contain data read from transaction data sources or a terminal. These fields are specified by the FIXFORM, FREEFORM, PROMPT, and CRTFORM statements. They remain incoming data fields even if their values are changed by COMPUTE statements.
Data source fields contain data stored in the data source. Their field names are prefaced by the D. prefix.
Temporary fields are created by and receive their values from COMPUTE statements.

The following request uses all three types of fields. The request awards a bonus of $150 to employees who received salary raises:

    MODIFY FILE EMPLOYEE 
1.  PROMPT EMP_ID CURR_SAL
    COMPUTE 
2.    BONUSAL/D8.2 = CURR_SAL + 150;
    MATCH EMP_ID
      ON NOMATCH REJECT
      ON MATCH COMPUTE 
3.  CURR_SAL = IF CURR_SAL GT D.CURR_SAL
               THEN BONUSAL
               ELSE CURR_SAL;
      ON MATCH UPDATE CURR_SAL
    DATA

The numbers above refer to these fields:

The EMP_ID and CURR_SAL fields are incoming data fields, because they are read by a PROMPT statement.
The BONUSAL field is a temporary field, because it is created by and receives its value from a COMPUTE statement.
The D.CURR_SAL field is a data source field, since its field name is prefaced with the D. prefix.

You may use COMPUTE statements to adjust the values of incoming data fields. For example, your MODIFY request reads salary values from a data source and places them into the field SALARY. You want to increase all these values by 10%. To do so, add this statement to the request:

COMPUTE SALARY = SALARY * 1.1;

In cases where the same field name exists in more than one segment, and that field must be redefined, the REDEFINES command should be used.

You may use the COMPUTE statement to define an unlimited number of temporary fields. For example, you define a temporary field TEMPSAL to contain the number 25000 if an employee is in the MIS department and the number 18000 if an employee is in the PRODUCTION department:

COMPUTE
  TEMPSAL =IF DEPARTMENT IS 'MIS' THEN 25000
      ELSE IF DEPARTMENT IS 'PRODUCTION' THEN 18000;

Note that MODIFY requests allow the use of up to 3,072 fields within the request. The number includes:

Data source fields referred to in the request.
Temporary fields created by COMPUTE and VALIDATE statements.
Temporary fields created automatically by FOCUS. These include:
- FOCURRENT for MODIFY requests run in Simultaneous Usage mode. FOCUS creates one FOCURRENT variable per request.
- REPEATCOUNT for MODIFY requests containing REPEAT statements. FOCUS creates one REPEATCOUNT variable per request regardless of the number of REPEAT statements.
- HOLDCOUNT and HOLDINDEX for MODIFY requests containing HOLD statements. FOCUS creates one HOLDCOUNT and one HOLDINDEX variable per request regardless of the number of HOLD statements.

Each field referred to or created in a MODIFY request counts as one field toward the 3,072 total, regardless of how often its value is changed by COMPUTE and VALIDATE statements. However, if a data source field is read by a FIXFORM, FREEFORM, PROMPT, or CRTFORM statement and also has its value changed by COMPUTE and VALIDATE statements, it counts as two fields.

FOCUS compiles most COMPUTE and DEFINE calculations when the request is parsed. Typically, the new compilation logic executes the compiled calculations in about one-fifth the time required by uncompiled calculations. However, the compiled form requires more memory. For this reason, very large MODIFY procedures may require more virtual storage to run and, should the MODIFY procedures be compiled, they will occupy more disk space.

There are two places in the MODIFY request where you can use COMPUTE statements:

At the beginning of the request. COMPUTE statements here define temporary field values for every transaction. Note that these statements may not perform calculations on data source field values (D. fields).
In or following MATCH and NEXT statements. COMPUTE statements here define temporary field values for transactions depending whether or not the MATCH or NEXT statement selected a particular segment instance. These statements may perform calculations using data source field values.

This section covers:

The syntax of COMPUTE statements.
Use of COMPUTE statements in MATCH and NEXT statements.
Modifying transaction fields.
Defining non-data source transaction fields.

Syntax: How to Use a COMPUTE Statement

The syntax of the COMPUTE statement is as follows (note that you can place several COMPUTE statements after the COMPUTE keyword):

COMPUTE 
field[/format] = expression; 
field[/format] = expression;
.
.
.

where:

field

Is the name of the field being set to the value of expression. The field can be an incoming data field or it can be a temporary field (whose name must be different from the incoming field names). Fields can only modify data source fields with the same name.

format

Is the format of the field if the field is temporary. Specify the format when defining the temporary field for the first time. Field formats are described in the Describing Data manual.

You can specify the MISSING option to declare temporary field values missing if values in the expression are missing. The MISSING option is discussed in the Creating Reports manual.

You can specify the YRTHRESH and DEFCENT options to handle cross-century dates. Using these options, and working with cross-century dates, is discussed in the Developing Applications manual.

expression;

Is any expression valid in a DEFINE or TABLE COMPUTE statement. In addition, you may use the FIND and LOOKUP functions, described in Special Functions.

Note: The expression can be null; that is, the COMPUTE statement can have the form

COMPUTE field/format=;

where format is the format of the field. This form is used to define transaction fields that are not listed in the Master File.

Note that you must terminate the expression with a semi-colon (;). You may type a COMPUTE statement over as many lines as you need, terminating the expression with a semi-colon. The COMPUTE command supports other attributes such as DFC, YRT, and MISSING. See the Creating Reports manual for details.

For example:

COMPUTE
CURR_SAL = IF CURR_JOBCODE IS A02 THEN 15000
  ELSE IF CURR_JOBCODE IS B02 THEN 17000
  ELSE IF CURR_JOBCODE IS B12 THEN 18000
  ELSE 20000;

In the preceding example, the temporary field CURR_SAL will contain 15000, 17000, 18000, or 20000, depending on the value of CURR_JOBCODE. CURR_SAL will then be used later in the MODIFY request.

You can also place an expression on the same line as a COMPUTE keyword, and several expressions on one line (ending each expression with a semicolon). For example:

COMPUTE CURR_SAL=CURR_SAL*1.2; ED_HRS = ED_HRS-5;

You can specify the MISSING option to declare temporary field values missing if values in the expression are missing. The MISSING option is discussed in the Creating Reports manual.

Using the COMPUTE Statement

How to:

Define Non-Data Source Transaction Fields

The following examples show how to use the COMPUTE statement.

Example: Placing COMPUTE Phrases in MATCH and NEXT Statements

You may place COMPUTE statements in MATCH and NEXT statements. The request only performs the computation if the MATCH or NEXT condition is met. These COMPUTE phrases may perform calculations on data source field values if these fields are either in the segment instance being modified or in a parent instance along the segment path (the parent instance, the parent's parent, and so on until the root segment). To specify data source field values (as opposed to values in the transaction field with the same name), affix the D. prefix to the front of the field name.

Note that COMPUTE statements that follow a MATCH or NEXT statement may also perform calculations on data source field values if these fields are in the instance selected by the previous statement (or are in the segment path).

When using MATCH WITH-UNIQUES followed by ON MATCH COMPUTE, each computed field must have its own ON MATCH COMPUTE statement.

The following request calculates employees' new salaries giving them a 10% increase over their present salaries. It only performs this calculations for employees whose IDs are stored in the data source:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH COMPUTE
     CURR_SAL = D.CURR_SAL * 1.1;
  ON MATCH UPDATE CURR_SAL
DATA

Example: Changing Incoming Data

You can use the COMPUTE statement to change incoming data. For example, assume you are preparing a MODIFY request to input new salaries into the data source. Just recently, the company granted employees in the MIS department an extra 3% pay raise. Rather than manually recalculating the new salaries for MIS employees, you can include a COMPUTE statement to do it for you:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID CURR_SAL DEPARTMENT
COMPUTE
CURR_SAL = IF DEPARTMENT IS 'MIS'
    THEN CURR_SAL * 1.03
    ELSE CURR_SAL;
MATCH EMP_ID
  ON MATCH UPDATE CURR_SAL
  ON NOMATCH REJECT
DATA

The new salary of employees who work in the MIS department will be 1.03 times more what they would have received ordinarily. Everybody else gets a normal raise.

Syntax: How to Define Non-Data Source Transaction Fields

If the names of incoming data fields are not listed in the Master File describing the data source, you must define them to FOCUS before they are read in by a FIXFORM, FREEFORM, PROMPT, or CRTFORM statement. Otherwise, FOCUS rejects the fields as unidentifiable and terminates the request.

To define the fields to FOCUS, specify them with the COMPUTE statement using the notation

COMPUTE field/format=;

where:

field: Is the incoming data field you want to define to FOCUS.
format: Is the format of the field. Field formats are described in the Describing Data manual.

Because there is no expression after the equal sign (=), the request reads the statement before it reads the incoming data. All COMPUTE statements having expressions are executed after the request reads the incoming data.

For example, you want to record promotions to the MIS and Production Departments in the data source. However, the transaction data source you are working with lists the departments by code, not by name: a 1 for MIS and a 2 for Production. You prepare the following MODIFY request:

MODIFY FILE EMPLOYEE
COMPUTE DEPCODE/I1=;
PROMPT EMP_ID DEPCODE
COMPUTE
  DEPARTMENT = IF DEPCODE IS 1 THEN 'MIS' ELSE 'PRODUCTION';
MATCH EMP_ID
  ON MATCH UPDATE DEPARTMENT
  ON NOMATCH REJECT
DATA

The first COMPUTE statement defines the incoming DEPCODE field to FOCUS. The second COMPUTE statement sets the value of the transaction field DEPARTMENT depending on the value of DEPCODE. This DEPARTMENT field then updates the DEPARTMENT field in the data source.

Compiling MODIFY Expressions Using Native Arithmetic

How to:

Control Compilation of MODIFY Expressions

Reference:

Usage Notes for SET MODCOMPUTE

The native compiler for MODIFY processes COMPUTE, IF, and VALIDATE expressions using the arithmetic operations built into the underlying operating system. This native compiler eliminates internal format conversions and speeds up expression processing. It significantly enhances the speed of expressions that use long packed fields and date fields.

Note: Expression compilers for MODIFY are supported only in Mainframe environments. Linux on the Mainframe does not support these compilers.

Syntax: How to Control Compilation of MODIFY Expressions

SET MODCOMPUTE={NATV|NEW|OLD}

where:

NATV: Activates the native compiler for MODIFY expressions. NATV is the default value.
NEW: Compiles MODIFY expressions using the standard FOCUS compilation routines, which use high-precision floating point format for all arithmetic operations.
OLD: Does not compile MODIFY expressions.

Reference: Usage Notes for SET MODCOMPUTE

The following are usage notes for SET MODCOMPUTE:

SET MODCOMPUTE can be issued in a user or system profile or on the command line.
Expressions using the following features are not compiled by the native compiler:
LIKE operator.

DEFINE functions.

LAST function.

Validating Transaction Values: The VALIDATE Statement

How to:

Use a VALIDATE Statement
Take Action on Invalid Data: The ON INVALID Phrase

Reference:

Using VALIDATE to Test Incoming Data

Most applications require that data be checked for accuracy before it is accepted into the data source. The VALIDATE statement checks values against certain conditions. If the value fails the test, the request rejects the transaction and displays a warning to the user.

For example, assume you are preparing a MODIFY request to update MIS and Production Department salaries in the data source. No one in those departments is ever paid less than $6,000 per year or more than $50,000. You can use the VALIDATE statement to reject those values that fall outside this range, such as a $700 or a $75,000 salary.

VALIDATE statements work the same way as COMPUTE statements: they set the value of a temporary field to the value of an expression. The only difference is that if the field value is set to 0, FOCUS rejects the transaction being processed and displays this message

(FOC421) TRANS n REJECTED INVALID rcode

where:

n: Is the number of the transaction being tested.
rcode: Is the variable receiving the test value.

The simplest way to use VALIDATE statements is to have them test the values of incoming data fields. If an incoming value is unacceptable, assign the temporary field a value of 0. Otherwise, assign the field a non-zero value. Note that the temporary field retains its value after the VALIDATE statement, and you may use this value in other calculations.

Tests provided by the DBA functions, which control access to data sources, function as involuntary VALIDATE tests and produce similar error messages.

You can place VALIDATE statements in two places in MODIFY requests:

At the beginning of the request. VALIDATE statements here test every transaction, discarding those containing invalid values. Expressions in these VALIDATE statements cannot use data source field values (D. fields).
In MATCH and NEXT statements. VALIDATE statements here test the transaction depending whether or not the MATCH or NEXT statement selected a particular segment instance. Expressions in these VALIDATE statements can use data source field values.

If you are validating fields in a repeating group and one field is rejected, all fields in the repeating group are rejected. However, if you are validating the fields in a MATCH or NEXT statement and one field is rejected, the other fields are not rejected.

If the MODIFY request prompts for data (the PROMPT statement), it is a good idea to validate each field after prompting. If you validate several fields at once, users must enter data for all the fields before the values they enter are tested. If one data value is invalid, they must reenter all the data values. If you validate each field, users will be warned as soon as they enter an invalid value, and the request will reprompt them for the correct value.

This section describes:

VALIDATE statement syntax.
Using the VALIDATE statement to validate incoming data.
Use of the ON INVALID phrase.
Use of VALIDATE statements in MATCH and NEXT statements.
Testing for the presence of incoming data.
Use of the DECODE function in VALIDATE statements.

If you validate data entered on a CRTFORM, invalid values cause the CRTFORM screen to be redisplayed along with the data you entered. This allows you to correct the data and re-enter it. You can deactivate this feature using the DEACTIVATE INVALID feature described in Active and Inactive Fields.

Syntax: How to Use a VALIDATE Statement

The syntax of the VALIDATE statement is as follows (note that you may include several VALIDATE statements after the VALIDATE keyword)

VALIDATE
  field[/format] = expression;
  field[/format] = expression;
     .
     .
     .

where:

field

Is the name of the temporary field. If this field is set to 0, FOCUS rejects the transaction being processed. Do not use an incoming field name or data source field name for this name.

format

Is the format of the field. The format type must be numeric (I, F, D, or P. Formats are described in the Describing Data manual). You need to specify the format only if you will use the field elsewhere in the request.

expression;

Is any expression valid in a DEFINE or TABLE COMPUTE statement (see the Creating Reports manual). Also, you may use the LOOKUP and FIND function described in Special Functions. If the value of the expression is 0, FOCUS rejects the transaction being processed. Note that you must terminate the expression with a semicolon (;).

You may specify the MISSING option to declare temporary field values missing if values in the expression are missing. The MISSING option is discussed in the Creating Reports manual.

Reference: Using VALIDATE to Test Incoming Data

You use VALIDATE statements most often to test incoming data values, assigning the temporary field a value of 0 if a value is not acceptable. The test expression can span several lines, but it must end with a semi-colon (;). Tests you can use in VALIDATE expressions are:

IF...THEN...ELSE statements.
Arithmetic expressions.
Logical expressions.
User functions and subroutines.
DECODE functions.
FIND and LOOKUP functions (see Special Functions).

You can use IF...THEN...ELSE statements in VALIDATE expressions (up to 16 statements per expression), such as:

SALTEST = IF SALARY LT 50000 THEN 1 ELSE 0;

If the incoming SALARY value is less than $50,000, the SALTEST temporary field is set to 1. If SALARY is $50,000 or greater, SALTEST is set to 0 and the transaction is rejected. Note that you may use all operations in VALIDATE IFºTHENºELSE statements that you use in COMPUTE and DEFINE statements (see the Creating Reports manual). Also note that all alphanumeric literals must be enclosed in single quotation marks.

Example: Using Logical Expressions

If an expression is evaluated as true, the temporary field is set to 1. Otherwise, the field is set to 0. For example:

SALTEST = SALARY LT 50000;

Note that you can use AND and OR operands in logical expressions, as discussed in the Creating Reports manual. For example:

SALTEST = (SALARY LT 50000) AND (JOB EQ 'B12');

If the incoming salary value is less than $50,000 and the job code is B12, SALTEST is set to 1. Otherwise, SALTEST is set to 0.

Example: Using the DECODE Function

This function allows you to compare an incoming field value against a list of acceptable and unacceptable values. For example:

SALTEST = DECODE JOBCODE (A03 0 B07 0 B12 0 ELSE 1);

If the incoming job code is A03, B07, or B12, SALTEST is set to 0.

Example: Using the FIND Function

This function searches another FOCUS data source for the presence of the incoming field value. If the value is there, the temporary field is set to a non-zero value; otherwise the field is set to 0. For example:

SALTEST = FIND(EMP_ID IN EDUCFILE);

If the incoming employee ID value is not present in the EDUCFILE data source, SALTEST is set to 0. The FIND function is discussed in Special Functions.

The following MODIFY request validates the DEPARTMENT and CURR_SAL fields:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID DEPARTMENT CURR_SAL
VALIDATE
  DEPTEST = IF DEPARTMENT IS 'MIS' THEN 1 ELSE 0;
  SALTEST = CURR_SAL LT 50000;
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH UPDATE CURR_SAL
DATA

This request will only accept your transactions if you enter MIS for the DEPARTMENT field and a value less than 50,000 for the CURR_SAL field.

Syntax: How to Take Action on Invalid Data: The ON INVALID Phrase

If a VALIDATE statement invalidates a transaction, you may take action using the ON INVALID phrase. This phrase allows you to:

Branch to another case using Case Logic. Case Logic is discussed in Case Logic.
Type a message. Typing messages are discussed in Messages: TYPE, LOG, and HELPMESSAGE.

The ON INVALID phrase immediately follows the validate statement. The syntax is

ON INVALID GOTO casename 
ON INVALID PERFORM casename 
ON INVALID TYPE [ON ddname]

where:

GOTO casename: Branches to another case called casename. GOTO also takes other options described in Branching to Different Cases: The GOTO, PERFORM, and IF Statements.
PERFORM casename: Branches to another case called casename. Execution then continues with the next statement after ON INVALID. PERFORM also takes other options discussed in Branching to Different Cases: The GOTO, PERFORM, and IF Statements.
TYPE [ON ddname]: Displays a message of up to four lines on the terminal. If you use the ON ddname option, the request writes the message to a sequential data source allocated to ddname.

This request updates employee salaries. It warns you when you have entered a salary that fails its validation test:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID CURR_SAL
VALIDATE
  SALTEST = IF CURR_SAL GT 50000 THEN 0 ELSE 1;
  ON INVALID TYPE
     "YOU ENTERED A SALARY HIGHER THAN $50,000"
     "THIS SALARY IS TOO HIGH"
     "PLEASE REENTER THE EMPLOYEE ID AND SALARY"
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH UPDATE CURR_SAL
DATA

VALIDATE Phrases in MATCH and NEXT Statements

How to:

Validate Values From a List: The DECODE Function

You may place VALIDATE statements in MATCH and NEXT statements. The request only performs the validation if the MATCH or NEXT condition is met. These VALIDATE phrases may use data source fields if these fields are either in the segment instance being modified or in a parent instance along the segment path (the parent instance, the parent's parent, and so on until the root segment). To specify data source field values, affix the D. prefix to the front of the field name.

Note that VALIDATE statements that follow a MATCH or NEXT statement may also use data source fields if these fields are in the instance selected by the previous statement (or are in the segment path).

This request makes sure that an employee's new salary is not less than the present salary after it ascertains that the employee's ID is recorded in the data source:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH PROMPT CURR_SAL
  ON MATCH VALIDATE
     SALTEST = IF CURR_SAL GE D.CURR_SAL THEN 1
               ELSE 0;
  ON MATCH UPDATE CURR_SAL
DATA

Example: Testing for the Presence of Transaction Data

You may test for missing data values in transactions using the MISSING feature in IF and WHERE phrases, described in the Creating Reports manual. These features determine whether an incoming field is present in the transaction or not, and are especially useful when the transactions are in a transaction data source.

This request rejects transactions without a job code:

MODIFY FILE EMPLOYEE
FREEFORM EMP_ID CURR_JOBCODE CURR_SAL
VALIDATE
  JOBTEST = IF CURR_JOBCODE IS NOT MISSING THEN 1
            ELSE 0;
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH UPDATE CURR_JOBCODE CURR_SAL
DATA
EMP_ID=071382660, CURR_JOBCODE=A13, CURR_SAL=18500.00, $
EMP_ID=112847612,                   CURR_SAL=19200.50, $
END

Syntax: How to Validate Values From a List: The DECODE Function

The DECODE function allows you to compare incoming data values against a list of acceptable and unacceptable values. This function is described in the Creating Reports manual. This section discusses how best to use the DECODE function to validate data.

The syntax of the DECODE function is

field = DECODE fieldname (code1 result1...[ELSE default])

where:

field: Is the name of the temporary field. If the field is set to 0, the transaction is rejected. Do not use an incoming field name or data source field name for this name.
fieldname: Is the incoming data field being tested.
code1 ...: Is the list of possible values.
result1: Is the number that the temporary field is set to if the incoming field has the preceding value. Place a 0 after invalid values; place a non-zero number after valid values.
ELSE: Indicates what the temporary field is set to if the incoming field does not have a value on the list. This list may have up to 32,767 literals.

For example, you want to record promotions to various company departments in the data source. There are five possible departments: Marketing, Accounting, Shipping, Sales, and Data Processing. You prepare this MODIFY request:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID DEPARTMENT
VALIDATE
  DEPTEST = DECODE DEPARTMENT (MARKETING 1
        ACCOUNTING 1 SHIPPING 1 SALES 1 MIS 1
        ELSE 0);
MATCH EMP_ID
  ON MATCH UPDATE DEPARTMENT
  ON NOMATCH REJECT
DATA

This request accepts MARKETING, ACCOUNTING, SHIPPING, SALES, and MIS as valid incoming values for the field DEPARTMENT, but rejects all other values.

You may also store the values in a separate file. The file must consist of stacked pairs of values, the values in each pair separated by a comma or spaces (you may want to arrange them in columns, see the example below). The left member of each pair is a possible value and the right member is the value that the temporary field is set to should the incoming data field have the value on the left.

The syntax of this form of the DECODE command is

field = DECODE infield (ddname ELSE m)

where:

field: Is the name of the temporary field. If the field is set to 0, the transaction is rejected. Do not use an incoming or data source field name for this name.
infield: Is the incoming field being tested.
ddname: Is the ddname of the file containing the list of possible values. The file may contain up to 32,767 bytes.
m: Is the value of field if the incoming data value is not in the list.

Below is a sample DECODE file.

MARKETING   1
ACCOUNTING  1
SHIPPING    1
SALES  1
MIS 1

Special Functions

How to:

Test for the Existence of Indexed Values in FOCUS Data Sources: The FIND Function

There are two functions that you can use only in MODIFY COMPUTE and VALIDATE statements. They are:

The FIND function, which tests for the existence of indexed values in FOCUS, relational, or Adabas data sources.
The LOOKUP function, which tests for the existence of non-indexed values in cross-referenced FOCUS, relational, or Adabas data sources and makes these values available for other computations.

Note: The LAST function in MODIFY can be used in COMPUTEs and VALIDATEs, in combination with FREEFORM or FIXFORM, to test incoming transaction values against those from a previously read record. For further information on the LAST function see the Creating Reports manual.

Syntax: How to Test for the Existence of Indexed Values in FOCUS Data Sources: The FIND Function

The FIND function verifies if an incoming data value is in a FOCUS data source field, whether the field is in the data source you are modifying or in another data source. The function sets a temporary field to a non-zero value if the incoming value is in the data source field and 0 if it is not. Note that a value greater than zero confirms the presence of the data value, not the number of instances in the data source field. You can then test and branch on this field using Case Logic, described in Case Logic.

Note that the data source field you are searching must be indexed, and that the FIND function does not work on data sources with different DBA passwords.

The syntax of the FIND function is

field = FIND(fieldname [AS dbfield] IN file);

where:

field: Is the name of the temporary field.
fieldname: Is the full name (not the alias or a truncation) of the incoming field being tested.
AS dbfield: Is the full name (not the alias or a truncation) of the data source field containing values to be compared with the incoming data field. This field must be indexed. If the incoming field and the data source field have the same name, you can omit this phrase.
file: Is the name of the data source.

Note that there can be no space between FIND and the left parenthesis.

The opposite of FIND is NOT FIND. The NOT FIND function sets a temporary field to 1 if the incoming value is not in the data source and 0 if the incoming value is in the data source. Its syntax is

field = NOT FIND(infield [AS dbfield] IN file)

where field, infield, dbfield, and file were explained previously.

You can use any number of FIND functions in COMPUTE and VALIDATE statements. However more FIND functions increase processing time and require more buffer space in core.

This request tests if each employee ID entered is also in the EDUCFILE data source. It then displays a message informing you whether it found the ID in the data source or not.

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
COMPUTE
  EDTEST = FIND(EMP_ID IN EDUCFILE);
  MSG/A40 = IF EDTEST IS 1 THEN
          'STUDENT LISTED IN EDUCATION FILE' ELSE
          'STUDENT NOT LISTED IN EDUCATION FILE';
MATCH EMP_ID
  ON NOMATCH TYPE "<MSG"
  ON MATCH TYPE "<MSG"
DATA

Example: Using the FIND Function in VALIDATE Statements

You may use the FIND function in a VALIDATE statement to test if a transaction field value exists in another FOCUS data source. If the field value is not in that data source, the function returns a value of 0, causing the validation to fail and the request to reject the transaction.

This request updates the number of hours spent by employees in class. It rejects employees not listed in the EDUCFILE data source, which records class attendance:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID ED_HRS
VALIDATE
  EDTEST = FIND(EMP_ID IN EDUCFILE);
MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH UPDATE ED_HRS
DATA

This VALIDATE statement will discard any incoming EMP_ID value not found in the EDUCFILE data source.

Reading Cross-Referenced FOCUS Data Sources: The LOOKUP Function

How to:

Use an Extended Syntax With LOOKUP

Reference:

Using the LOOKUP Function in VALIDATE Statements

The LOOKUP function retrieves data values from cross-referenced data sources, both data sources cross-referenced statically in the Master File and data sources joined dynamically by the JOIN command. The LOOKUP function is necessary because, unlike TABLE requests, MODIFY requests cannot read cross-referenced data sources freely. With the LOOKUP function, the requests can use the data in computations and in messages but cannot modify cross-referenced data sources; to modify more than one data source in one request, use the COMBINE command discussed in Modifying Multiple Data Sources in One Request: The COMBINE Command.

The LOOKUP function can read cross-referenced segments that are linked directly to a segment in the host data source (the host segment). This means that the cross-referenced segments must have segment types of KU, KM, DKU, or DKM (but not KL or KLU) or contain the cross-referenced field specified by the JOIN command (see the Describing Data manual).

The cross-referenced segment contains two fields of interest:

The field containing the values you want. This is the field the LOOKUP function specifies. For example, this LOOKUP function retrieves values from the DATE_ATTEND field:
```
RTN = LOOKUP(DATE_ATTEND);
```
The cross-referenced field. This field shares values with a field in the host segment called the host field. These two fields link the host segment to the cross-referenced segment. The LOOKUP function uses the cross-referenced field, which is indexed, to locate a specific segment instance.

To use the LOOKUP function, the MODIFY request reads a transaction value for the host field. The LOOKUP function then searches the cross-referenced segment for an instance containing this value in the cross-referenced field:

If there are no such instances, the function sets a return variable to 0. If you use the field specified by the LOOKUP function in the request, the field assumes a value of blank if alphanumeric and 0 if numeric.
If there are instances (there can be more than one if the cross-referenced segment type is KM, DKM, or if you specified the ALL keyword in the JOIN command), the function sets the return variable to one and retrieves the value of the specified field from the first instance it finds.

The syntax of the LOOKUP function is

rcode = LOOKUP(field);

where:

rcode: Is a variable you specify to receive a return code value. This value is 1 if the LOOKUP function can locate a cross-referenced segment instance, 0 if the function cannot.
field: Is the field that you want to retrieve in the cross-referenced data source. Note that this field name cannot exist in the host data source, and that the LOOKUP function may specify only one field at a time. Each field you wish to retrieve requires a separate LOOKUP function. To look up all fields in the cross-referenced segment, use LOOKUP (SEG.field).

Note that there may be no space between LOOKUP and the left parenthesis. The LOOKUP function can exist by itself or as part of a larger expression. If it exists by itself, it must terminate with a semicolon.

For example, you wish to update the amount of classroom hours employees have spent. Because of a new system of accounting, employees taking classes after January 1, 1985 are to be credited with 10% more classroom hours than their records indicate.

The employee IDs (EMP_ID) and classroom hours (ED_HRS) are located in the host segment. The class dates (DATE_ATTEND) are located in the cross-referenced segment. The shared field is the employee ID field.

The data source structure is shown in this diagram:

The request is:

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

A sample execution of this request might go as follows:

The request prompts you for an employee ID and number of class hours. You enter the ID 117593129 and 10 class hours.
The LOOKUP function locates the first instance in the cross-referenced segment containing the employee ID 117593129. Since the instance exists, the function returns a 1 to the EDTEST variable. This instance lists the class date as 821028 (October 28, 1982).
The LOOKUP function retrieves the value 821028 for the DATE_ATTEND field.
The COMPUTE statement tests the value of the DATE_ATTEND field. Since October 28, 1982 is after January 1, 1982, the statement increases the incoming ED_HRS value from 10 to 11 hours.
The request updates the classroom hours for employee 117593129 using the new ED_HRS value.

You may also use a data source value in a specific host segment instance to search the cross-referenced segment. To do this, prepare the request this way:

In the MATCH statement that selects the host segment instance, activate the host field. This can be done with the ACTIVATE phrase (discussed in Active and Inactive Fields).
In the same MATCH statement, place the LOOKUP function after the ACTIVATE phrase.

This request displays the employee IDs, dates of salary raises, employee names, and the position each employee held after the raise was granted:

The employee IDs and names (EMP_ID) are in the root segment.
The date of raise (DAT_INC) is in the descendant host segment.
The job titles are in the cross-referenced segment.
The shared field is JOBCODE. You never enter any job codes; the values are all 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 might go as follows:

The request prompts you for an employee ID and date of pay raise. You enter employee ID 071382660 and date of raise 820101 (January 1, 1982).
The request locates the instance containing the ID 071382660, then locates the child instance containing the date of raise 820101.
This child instance contains the job code A07. The ACTIVATE statement activates this value, making it available to the LOOKUP function.
The LOOKUP function locates the job code A07 in the cross-referenced segment. It returns a 1 into the RTN variable and retrieves the corresponding job description of SECRETARY.

The request displays the values using a TYPE statement:

EMPLOYEE ID:    071382660
DATE INCREASE:  82/01/01
NAME:           ALFRED STEVENS
POSITION:       SECRETARY

Note: You may also need to activate the host field if you are using the LOOKUP function within a NEXT statement. This request, similar to the previous one except for the NEXT statement, displays the latest position held by a particular 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

Syntax: How to Use an Extended Syntax With LOOKUP

If the function cannot locate a value of the host field in the cross-referenced segment, you may specify that the LOOKUP function locate the next highest or lowest cross-referenced field value in the cross-referenced segment by using an extended syntax.

To use this LOOKUP feature, the index must have been created on FOCUS Release 4.5 or later with the INDEX parameter set to NEW (the binary tree scheme). To determine what type of index your data source uses, enter the ? FDT command (see the Developing Applications manual).

Note that a field retrieved by the LOOKUP function does not require the D. prefix to be displayed in TYPE statements. FOCUS treats the field value as a transaction value.

The extended syntax of the LOOKUP function is

COMPUTE
  rcode = LOOKUP(field operator);

where:

rcode

Is a variable you specify to receive a return code value. (The value the variable receives depends on the outcome of the function below.)

field

Is the name of the field you want to use in MODIFY computations. Note that this cannot be the cross-referenced field.

operator

These parameters specify the action the request takes if there is no cross-referenced segment instance corresponding to the host field value. The actions can be one of the following:

EQ causes the LOOKUP function 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 the LOOKUP function to locate the instance with the exact or next highest value of the cross-referenced field.

LE causes the LOOKUP function to locate the instance with the exact or next lowest value of the indexed field.

Note that there can be no space between LOOKUP and the left parenthesis.

This table summarizes the value of rcode depending on which instance the LOOKUP function locates:

Action	rcode value
Exact cross-referenced value located	1
Next highest cross-referenced value located	2
Next lowest cross-referenced value located	-2
Cross-referenced field value not located	0

Reference: Using the LOOKUP Function in VALIDATE Statements

When you use the LOOKUP function, you may want to 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 statement. If the function cannot locate the instance in the cross-referenced segment, it sets the value of the return variable to 0. This causes the request to reject the transaction.

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

The request is:

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

If the employee is not recorded in the cross-referenced segment, then the employee has never attended a class. This means that a transaction recording the employee's classroom hours is an error, and should be rejected.

This is the purpose of the LOOKUP function in the VALIDATE statement. If the function cannot locate an employee's record in the cross-referenced segment, it returns a 0 to the TEST_DATE field. This causes the request to reject the transaction.