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:


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:

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

Top of page

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 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
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
.
.
.

WebFOCUS