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.
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:
Is the first data source from which MATCH retrieves requested records.
Specifies how the retrieved records from the files are to be compared. For details, see Merge Phrases.
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.
For details, see Displaying Report Data.
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 |