The MATCH Command

In response to a MATCH command, the data adapter selects the first row in the table that meets the MATCH criteria. The MATCH command compares incoming data with one or more field values and then performs actions that depend on whether or not a row with matching field values exists in the table.

In this section: Data Adapter MATCH Behavior How to: Use the MATCH Command in MODIFY Example: Using the MODIFY MATCH Command Reference: Acceptable Actions for MATCH

Syntax: How to Use the MATCH Command in MODIFY

The syntax of the MATCH command in MODIFY is

MATCH field1 [field2...fieldn]
  ON MATCH action_1
  ON NOMATCH action_2

where:

fieldn

Are fields representing columns. FOCUS compares incoming data values against existing column values. The fields can be any combination of key and/or non-key fields. Specify complete fieldnames; MATCH does not support truncated names.

action_1

Is the operation to perform when a row's values match the incoming data values.

action_2

Is the operation to perform when a row's existing values do not match the incoming data values.

Your FOCUS documentation on maintaining databases discusses these actions in detail.

MATCH processing for multi-table Master Files is the same as for a multi-segment FOCUS data source.

In Maintain, you do not have to include an ON NOMATCH command in order to reject a transaction; Maintain automatically rejects a transaction that does not satisfy the MATCH criteria. See your FOCUS documentation on maintaining databases for MATCH syntax in Maintain.

Reference: Acceptable Actions for MATCH

Acceptable actions for MATCH commands fall into eight groups. They are operations that:

• Include, change, or delete rows.
• Control MATCH processing, such as rejecting the current transaction.
• Read incoming data fields.
• Perform computations and validations, or type messages to the terminal.
• Control Case Logic.
• Control multiple-record processing.
• Activate and deactivate fields in MODIFY.
• Permanently store data in the RDBMS.

Data Adapter MATCH Behavior

In MODIFY requests, there are two major differences in the way MATCH commands function for the data adapter and for native FOCUS:

• With the data adapter, you can change the value of a table's primary key (subject to RDBMS limitations) using the UPDATE statement. When modifying a FOCUS data source, you cannot change key field values.
• You can MATCH on any field or combination of fields in the row. However, if the full primary key is not included in the MATCH criteria, the data adapter may retrieve more than one row as a result of the MATCH.

For example, if the primary key is EMP_ID and the incoming value for MATCH LAST_NAME is SMITH, the answer set contains all rows with last name SMITH.

Note: In Maintain, MATCH functions identically for the data adapter and for native FOCUS.

Example: Using the MODIFY MATCH Command

Consider a MODIFY request that maintains the EMPINFO table. It prompts for an employee ID and for a new salary; then it processes the incoming data. The annotated request contains the following MATCH commands:

    MODIFY FILE EMPINFO
    PROMPT EMP_ID CURRENT_SALARY
1.  MATCH EMP_ID
2.  ON MATCH UPDATE CURRENT_SALARY 
3.  ON NOMATCH REJECT
    DATA

The incoming transaction contains the following values:

EMP_ID = 123456789
CURRENT_SALARY = 20000

The request processes as follows:

1. The MATCH command compares the value of the incoming EMP_ID, 123456789, to the EMP_ID values in the rows of the EMPINFO table. Since EMP_ID is the primary key of this table, the RDBMS can return at most one row as a result of this MATCH.

2. If a row exists for EMP_ID 123456789, the MATCH command updates the CURRENT_SALARY value of that row with the incoming value 20000.

3. If no row exists for EMP_ID 123456789, the MATCH command rejects the transaction.