MATCH Processing With Common High-Order Sort Fields

When you construct your MATCH so that the first sort (BY) field (called the common high-order sort field) used for both data sources is the same, the match compares the values of the common high-order sort fields. If the entire sequence of sort fields is common to both files, all are compared.

At least one pair of sort fields is required. Field formats must be the same. In some cases, you can redefine a field format using the DEFINE command. If the field names differ, use the AS phrase to rename the second sort field to match the first. When the AS phrase is used in a MATCH request, the specified field is automatically renamed in the resulting HOLD file.

When you are merging files with common sort fields, the following assumptions are made:

If one of the sort fields is a subset of the other, a one-to-many relationship is assumed.
If neither of the sort fields is a subset of the other, a one-to-one relationship is assumed. At most, one matching record is retrieved.

Example: MATCH Processing With Common High-Order Sort Fields

To understand common high-order sort fields more clearly, consider some of the data from the following data sources

EMPLOYEE Data Source		EDUCFILE Data Source
EMP_ID	LAST_NAME	EMP_ID	COURSE_CODE
071382660	STEVENS	071382660	101
119329144	BANNING	212289111	103
112847612	SMITH	112847612	103

and this MATCH request:

MATCH FILE EMPLOYEE
SUM LAST_NAME BY EMP_ID
RUN 
FILE EDUCFILE
SUM COURSE_CODE BY EMP_ID
AFTER MATCH HOLD OLD-OR-NEW
END

MATCH processing occurs as follows:

Since there is a common high-order sort field (EMP_ID), the MATCH logic begins by matching the EMP_ID values in records from the EMPLOYEE and EDUCFILE files.
There are records from both files with an EMP_ID value of 071382660. Since there is a match, this record is written to the HOLD file:
```
Record n: 071382660 STEVENS 101
```
There are records from both files with an EMP_ID value of 112847612. Since there is a match, this record is written to the HOLD file:
```
Record n: 112847612 SMITH 103
```
The records do not match where a record from the EMPLOYEE file has an EMP_ID value of 119329144 and a record from the EDUCFILE file has an EMP_ID value of 212289111. The record with the lower value is written to the HOLD file and a space is inserted for the missing value:
```
Record n: 119329144 BANNING
```
Similarly, the 212289111 record exists only in the EDUCFILE file, and is written as:
```
Record n: 212289111 103
```

The following code produces a report of the records in the HOLD file:

TABLE FILE HOLD
PRINT *
END

The output is:

EMP_ID     LAST_NAME        COURSE_CODE
------     ---------        -----------
071382660  STEVENS          101
112847612  SMITH            103
117593129  JONES            203
119265415  SMITH            108
119329144  BANNING
123764317  IRVING
126724188  ROMANS
212289111                   103
219984371  MCCOY
315548712                   108
326179357  BLACKWOOD        301
451123478  MCKNIGHT         101
543729165  GREENSPAN
818692173  CROSS            302

Example: Merging With a Common High-Order Sort Field

This request combines data from the JOBFILE and PROD data sources. The sort fields are JOBCODE and PROD_CODE, renamed as JOBCODE:

MATCH FILE JOBFILE
PRINT JOB_DESC
BY JOBCODE
RUN 
FILE PROD
PRINT PROD_NAME
BY PROD_CODE AS 'JOBCODE'
AFTER MATCH HOLD OLD-OR-NEW
END

Example: Merging Without a Common High-Order Sort Field

If there are no common high-order sort fields, a match is performed on a record-by-record basis. The following request matches the data and produces the HOLD file:

MATCH FILE EMPLOYEE
PRINT LAST_NAME AND FIRST_NAME
BY EMP_ID
RUN 
FILE EMPLOYEE
PRINT EMP_ID
BY LAST_NAME BY FIRST_NAME
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT *
END

The retrieved records from the two data sources are written to the HOLD file; no values are compared. The output is:

EMP_ID    LAST_NAME       FIRST_NAME LAST_NAME       FIRST_NAME EMP_ID   
------    ---------       ---------- ---------       ---------- ------   
071382660 STEVENS         ALFRED     BANNING         JOHN       119329144
112847612 SMITH           MARY       BLACKWOOD       ROSEMARIE  326179357
117593129 JONES           DIANE      CROSS           BARBARA    818692173
119265415 SMITH           RICHARD    GREENSPAN       MARY       543729165
119329144 BANNING         JOHN       IRVING          JOAN       123764317
123764317 IRVING          JOAN       JONES           DIANE      117593129
126724188 ROMANS          ANTHONY    MCCOY           JOHN       219984371
219984371 MCCOY           JOHN       MCKNIGHT        ROGER      451123478
326179357 BLACKWOOD       ROSEMARIE  ROMANS          ANTHONY    126724188
451123478 MCKNIGHT        ROGER      SMITH           MARY       112847612
543729165 GREENSPAN       MARY       SMITH           RICHARD    119265415
818692173 CROSS           BARBARA    STEVENS         ALFRED     071382660