Merging Data

How to:

Reference:

You can merge two or more data sources, and specify which records to merge and which to sort out, using the MATCH command. The command creates a new data source (a HOLD file), into which it merges fields from the selected records. You can report from the new data source and use it as you would use any other HOLD file. However, you cannot specify a format for the HOLD file. It will be created as a BINARY or ALPHA HOLD file depending on the value of the HOLDFORMAT parameter. The merge process does not change the original data sources.

You select the records to be merged into the new data source by specifying sort fields in the MATCH command. You specify one set of sort fields (using the BY phrase), for the first data source, and a second set of sort fields for the second data source. The MATCH command compares all sort fields that have been specified in common for both data sources, and then merges all records from the first data source whose sort values match those in the second data source into the new HOLD file. You can specify up to 32 sort sets. This includes the number of common sort fields.

In addition to merging data source records that share values, you can merge records based on other relationships. For example, you can merge all records in each data source whose sort values are not matched in the other data source. Yet another type of merge combines all records from the first data source with any matching records from the second data source.

You can merge up to 16 sets of data in one Match request. For example, you can merge different data sources, or data from the same data source.

Note: The limit of 16 applies to the most complex request. Simpler requests may be able to merge more data sources.


Top of page

x
Syntax: How to Merge Data Sources

The syntax of the MATCH command is similar to that of the TABLE command:

MATCH FILE file1 
.
.
.
RUN 
FILE file2 
.
.
.
[AFTER MATCH merge_phrase]
RUN 
FILE file3 
.
.
.
[AFTER MATCH merge_phrase]
END

where:

file1

Is the first data source from which MATCH retrieves requested records.

merge_phrase

Specifies how the retrieved records from the files are to be compared. For details, see Merge Phrases.

file2/file3

Are additional data sources from which MATCH retrieves requested records.

Note that a RUN command must follow each AFTER MATCH command (except for the last one). The END command must follow the final AFTER MATCH command.

MATCH generates a single-segment HOLD file. You can print the contents of the HOLD file using the PRINT command with the wildcard character (*). For related information, see Merging Data Sources.


Top of page

x
Reference: Usage Notes for Match Requests


Example: Merging Data Sources
MATCH FILE EDUCFILE
SUM COURSE_CODE
BY EMP_ID
RUN
FILE EMPLOYEE
SUM LAST_NAME AND FIRST_NAME
BY EMP_ID BY CURR_SAL
AFTER MATCH HOLD OLD-OR-NEW
END
-******************************
-*  PRINT CONTENTS OF HOLD FILE
-******************************
TABLE FILE HOLD
PRINT *
END

The merge phrase used in this example was OLD-OR-NEW. This means that records from both the first (old) data source plus the records from the second (new) data source appear in the HOLD file.

Note that if you are working at the command line in Developer Studio, after you enter the command RUN, a message indicates how many records were retrieved, and if you are entering the MATCH request at the command line, prompts you for the name of the next data source to be merged.

The output is:

EMP_ID     COURSE_CODE         CURR_SAL  LAST_NAME        FIRST_NAME
------     -----------         --------  ---------        ----------
071382660  101               $11,000.00  STEVENS          ALFRED    
112847612  103               $13,200.00  SMITH            MARY      
117593129  203               $18,480.00  JONES            DIANE     
119265415  108                $9,500.00  SMITH            RICHARD   
119329144                    $29,700.00  BANNING          JOHN      
123764317                    $26,862.00  IRVING           JOAN      
126724188                    $21,120.00  ROMANS           ANTHONY   
212289111  103                     $.00                             
219984371                    $18,480.00  MCCOY            JOHN      
315548712  108                     $.00                             
326179357  301               $21,780.00  BLACKWOOD        ROSEMARIE 
451123478  101               $16,100.00  MCKNIGHT         ROGER     
543729165                     $9,000.00  GREENSPAN        MARY      
818692173  302               $27,062.00  CROSS            BARBARA   

WebFOCUS