Merging Concatenated Data Sources

In this section:

How to:

You can use the MORE phrase in a MATCH request to merge up to 16 sets of concatenated data sources.

You must meet all MATCH requirements in the main request. All data sources to be merged must be sorted by at least one field with a common format.

The MATCH request results in a HOLD file containing the merged data. You can specify how you want each successive file merged using an AFTER MATCH command. For example, you can retain:

Syntax: How to Merge Concatenated Data Sources 

1. MATCH FILE file1main request 
   MORE 
2. FILE file2subrequest 
   MORE 
3. FILE file3subrequest 
   RUN 
4. FILE file4main request 
5. [AFTER MATCH merge_phrase] 
   MORE 
6. FILE file5subrequest 
   MORE 
7. FILE file6subrequest 
   RUN 
8. FILE file7main request 
9. [AFTER MATCH merge_phrase]
   MORE 
10.FILE file8subrequest 
   MORE 
11.FILE file9subrequest 
   END
  1. Starts the first answer set in the MATCH. File1 is the first data source in the first answer set.
  2. Concatenates file2 to file1 in the first MATCH answer set.
  3. Concatenates file3 to file1 and file2 in the first MATCH answer set.
  4. Starts the second answer set in the MATCH. File4 is the first data source in the second answer set.
  5. All data concatenated in the first answer set is merged with the data concatenated in the second answer set using the AFTER MATCH merge_phrase in the second answer set.
  6. Concatenates file5 to file4 in the second MATCH answer set.
  7. Concatenates file6 to file4 and file5 in the second MATCH answer set.
  8. Starts the third answer set in the MATCH. File7 is the first data source in the third answer set.
  9. All merged data from the first and second answer sets, now a HOLD file, is merged with the data concatenated in the third answer set using the AFTER MATCH merge_phrase in the third answer set. This final set of merged data is stored in a HOLD file.
  10. Concatenates file8 to file7 in the third MATCH answer set.
  11. Concatenates file9 to file7 and file8 in the third MATCH answer set.

Using Sort Fields in MATCH Requests

If the data sources in the MATCH share common high-order sort fields with identical names and formats, the MATCH process merges records with matching sort field values from each of the files. If the two data sources in the MATCH have the same sort field with different names, you can change one of the names with an AS phrase.

If the files in the MATCH do not share a high-order sort field, the fields are not compared. Instead, the fields from the first record in each data source are merged to create the first record in the HOLD file, and so on for all remaining records.

Example: Merging Concatenated Data Sources With Common High-Order Sort Fields

The following annotated sample stored procedure illustrates MATCH with MORE, using a common sort field: 

1. DEFINE FILE EMPDATA
   CURR_SAL/D12.2M = CSAL;
   FIRST_NAME/A10 = FN;
   EID/A9 = PIN;
   END
 
   -*Start MATCH.
  
2. MATCH FILE EMPLOYEE
      SUM CURR_SAL AS 'CURRENT'
          FIRST_NAME AS 'FIRST'
      BY EID AS 'SSN'
   -*Concatenate file EMPDATA to EMPLOYEE to form first MATCH answer set. 
3.    MORE
      FILE EMPDATA
      RUN
   -*Second MATCH answer set:
  
4. FILE TRAINING
      PRINT EXPENSES 
5.    BY PIN AS 'SSN' 
6.    AFTER MATCH HOLD OLD-OR-NEW
   END
 
   -*Print merged file:
  
7. TABLE FILE HOLD
      PRINT *
   END
  1. Defines the EMPDATA fields needed for concatenating it to EMPLOYEE.
  2. Starts the MATCH and the main request in the concatenation. The main request defines all printing and sorting for the concatenated files. The sort field is called SSN in the resulting file.
  3. Concatenates file EMPDATA to EMPLOYEE. This concatenated file becomes the OLD file in the MATCH.
  4. Creates the NEW file in the MATCH.
  5. Uses an AS phrase to change the name of the sort field in the NEW file to the same name as the sort field in the OLD file.
  6. Defines the merge procedure. All records from the NEW file, the OLD file, and both files are included in the final HOLD file.
  7. Prints the values from the merged file.

The first page of output is: 

SSN                CURRENT  FIRST        EXPENSES
---                -------  -----        --------
000000010       $55,500.00  DANIEL       2,300.00
000000020       $62,500.00  MICHAEL             .
000000030       $70,000.00  LOIS         2,600.00
000000030       $70,000.00  LOIS         2,300.00
000000040       $62,500.00  RUTH         3,400.00
000000050       $54,100.00  PETER        3,300.00
000000060       $55,500.00  DORINA              .
000000070       $83,000.00  EVELYN              .
000000080       $43,400.00  PAMELA       3,200.00
000000080       $43,400.00  PAMELA       3,350.00
000000090       $33,000.00  MARIANNE            .
000000100       $32,400.00  TIM          3,100.00
000000110       $19,300.00  ANTHONY      1,800.00
000000110       $19,300.00  ANTHONY      2,500.00
000000110       $19,300.00  ANTHONY      2,400.00
000000120       $49,500.00  KATE         2,200.00
000000130       $62,500.00  MARCUS              .

Example: Merging Concatenated Data Sources Without a Common Sort Field

In this example, the merged data sources do not share a sort field: 

DEFINE FILE EMPDATA
CURR_SAL/D12.2M = CSAL;
FIRST_NAME/A10 = FN;
EID/A9 = PIN;
END
 
-*Start MATCH
 
MATCH FILE EMPLOYEE
SUM CURR_SAL AS 'CURRENT'
    FIRST_NAME AS 'FIRST'
BY EID AS 'SSN'
 
-*Concatenate EMPDATA to EMPLOYEE to form the first MATCH answer set
 
MORE
FILE EMPDATA
RUN 
 
-*Second MATCH answer set:
 
FILE TRAINING
PRINT EXPENSES
BY PIN AS 'EID'
AFTER MATCH HOLD OLD-OR-NEW
END
 
-*Print merged file:
 
TABLE FILE HOLD
PRINT *
END

The AS phrase changes the answer set. Since the sort fields no longer have the same names, the fields are merged with no regard to matching records.

The first page of output is: 

SSN                CURRENT  FIRST       EID         EXPENSES
---                -------  -----       ---         --------
000000010       $55,500.00  DANIEL      000000010   2,300.00
000000020       $62,500.00  MICHAEL     000000030   2,600.00
000000030       $70,000.00  LOIS        000000030   2,300.00
000000040       $62,500.00  RUTH        000000040   3,400.00
000000050       $54,100.00  PETER       000000050   3,300.00
000000060       $55,500.00  DORINA      000000080   3,200.00
000000070       $83,000.00  EVELYN      000000080   3,350.00
000000080       $43,400.00  PAMELA      000000100   3,100.00
000000090       $33,000.00  MARIANNE    000000110   1,800.00
000000100       $32,400.00  TIM         000000110   2,500.00
000000110       $19,300.00  ANTHONY     000000110   2,400.00
000000120       $49,500.00  KATE        000000120   2,200.00
000000130       $62,500.00  MARCUS      000000140   3,600.00
000000140       $62,500.00  VERONICA    000000150   3,400.00
000000150       $40,900.00  KARL        000000160   1,000.00
000000160       $62,500.00  ROSE        000000180   1,250.00
000000170       $30,800.00  WILLIAM     000000190   3,150.00