Menu: Found in the DATA menu
Use the Merge command to combine two sorted tables with identical record structures into a third table. For example, you can use Merge to combine two tables with identical record structures from different time periods, different company branches and so on.
To combine fields from a Master and Transaction table that have different structures, see Join.
Merge places the merged output records from both the primary and secondary tables into a third output table. All aspects of the records remain unaltered and the sort sequences are maintained. For each key field value, the records of the primary table are placed before those of the second.
The primary and secondary tables must have identical structures, although the fields for the two tables do not have to be named identically.
The Merge command produces a table along with a table layout. This new table can be used for further analysis and reporting with Analyzer.
Note: The If, While, First and Next parameters that limit records selected for output only apply to the primary table.
See Extract for other possible alternatives for merging data.
For command line use, you must open the secondary table, as well as the primary table, before issuing the Merge command.
The primary and secondary tables must have identical record structures. If this is not the case, the results are unpredictable.
Both the primary and secondary tables must be sorted in ascending sequence on common key character fields. You can check the Presort checkbox in the Merge dialog to sort the primary table. The primary table can be indexed instead of sorted, if desired.
The key fields must be character fields. If they are not, use the STRING() function to convert the key fields to character fields or the DATE() function to convert a date. The total length of the character fields must be the same in both tables.
The tables to be merged must be defined within the same Analyzer Project. When working with table data on an Arbutus Server, tables to be merged must reside on the same server. You cannot merge a table on the local drive to a table on the server.
Note: When using Analyzer to merge IMS segment paths from the same IMS data base, if a PCB isn’t available for each segment path then the message "Insufficient PCB's for multiple path processing" is displayed and the command in progress is cancelled. For more information, please see “The Arbutus zSeries Server Program Specification Block” in the Arbutus zSeries Server Installation Guide.
In addition to the command parameters described below, the Merge command has the following command parameters: Append, First, If, Open, Next, Noformat, To and While. For a description of these parameters, see Command Parameters. For a description of supported field modifiers see Field Modifiers.
In the command dialog, this option allows you to choose the secondary table from the drop down list that you want to merge with the primary table.
Note: For the Merge command, the secondary file must already be physically in the appropriate sorted order.
The PKEY command parameter specifies which key fields or expressions to merge from the primary table to use. In the command dialog, select the key fields from the “Merge Key Fields” primary list box or click [Choose] to display the Selected Fields dialog.
For more information on the Selected Fields dialog, see Dialog Boxes.
The PRESORT command parameter specifies that the primary table is to be sorted on the primary key fields before joining the tables. In the command dialog, select the Presort checkbox beside the primary field list box.
The SKEY command parameter specifies which key fields or expressions to merge from the secondary table to use. In the command dialog, select the key fields from the “Merge Key Fields” secondary list box or click [Choose] to display the Selected Fields dialog.
MERGE ON key-field-list¿
or
MERGE PKEY primary-key-fields SKEY secondary-key-fields¿
Note: Use the first variation of the command syntax when the key field names in the two tables are identical and are fields, not expressions. Otherwise, use the second variation.
You can add the following options to either syntax format:
TO create-table-name
<PRESORT>
<IF test> <WHILE test> <NEXT|FIRST range>
<NOFORMAT> <APPEND>
<OPEN>¿
Note: You cannot use the presort keyword within a group running in procedure mode.
To merge two years of accounts receivable data into one table (AR_2011 and AR_2010), ensure that both tables are sorted on the common account number key (No). Open AR_2011 as your primary table, then in the Merge dialog, select AR_2010 as your secondary table. Identify the account number field (No) as the key field for both tables. Click [OK] to create the merged table.
The new merged table, AR_Merge, lists (by account number) all of the primary records prior to those of the secondary records for each key value. All aspects of the records remain unaltered and the sort sequences are maintained.