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.

Top of page

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

Information Builders