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 EMPLOYEE and EMPDATA data sources. The sort fields are EID and PIN.

MATCH FILE EMPLOYEE
PRINT LN FN DPT
BY EID
RUN
FILE EMPDATA
PRINT LN FN DEPT
BY PIN
AFTER MATCH HOLD OLD-OR-NEW
END
 
TABLE FILE HOLD
PRINT *
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 EMPDATA
PRINT PIN
BY LASTNAME
BY FIRSTNAME
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	LASTNAME	FIRSTNAME	PIN
071382660	STEVENS	ALFRED	ADAMS	RUTH	000000040
112847612	SMITH	MARY	ADDAMS	PETER	000000050
117593129	JONES	DIANE	ANDERSON	TIM	000000100
119265415	SMITH	RICHARD	BELLA	MICHAEL	000000020
119329144	BANNING	JOHN	CASSANOVA	LOIS	000000030
123764317	IRVING	JOAN	CASTALANETTA	MARIE	000000270
126724188	ROMANS	ANTHONY	CHISOLM	HENRY	000000360
219984371	MCCOY	JOHN	CONRAD	ADAM	000000250
326179357	BLACKWOOD	ROSEMARIE	CONTI	MARSHALL	000000410
451123478	MCKNIGHT	ROGER	CVEK	MARCUS	000000130
543729165	GREENSPAN	MARY	DONATELLO	ERICA	000000320
818692173	CROSS	BARBARA	DUBOIS	ERIC	000000210
			ELLNER	DAVID	000000380
			FERNSTEIN	ERWIN	000000350
			GORDON	LAURA	000000180
			GOTLIEB	CHRIS	000000340
			GRAFF	ELAINE	000000390
			HIRSCHMAN	ROSE	000000160
			KASHMAN	YOLANDA	000000240
			LASTRA	KAREN	000000200
			LEWIS	CASSANDRA	000000220
. . .