MERGE command

Used to combine two sorted ACL tables with identical record structures into a new ACL table that uses the same sort order as the original tables.

Syntax

MERGE {ON key_fields |PKEY primary_key_fields SKEY secondary_key_fields}
TO table_name  <PRESORT> <IF test> <WHILE test>
<FIRST|NEXT range> <APPEND> <ISOLOCALE locale_code> <OPEN>

Parameters

ON key_fields

Specifies the fields to merge on if the key fields in the two tables have the same name.

PKEY primary_key_fields

Specifies the key field or fields, or expression, in the primary table.

SKEY secondary_key_fields

Specifies the key field or fields, or expression, in the secondary table.

TO table_name

Specify TO table_name to write the results to an ACL table. You must specify the table_name value as a quoted string with a .FIL file extension to create an ACL table.

For example: TO "Output.FIL"

You can also specify an absolute or relative file path to an existing folder to write the .FIL file to.

For example: TO "C:\Output.FIL" or TO "Results\Output.FIL"

PRESORT

Optional. Sorts the primary table on the primary key before executing the command. You cannot use PRESORT inside the GROUP command.

IF test

Optional. Specifies a condition that must be met. The command is only executed on records that pass the test.

WHILE test

Optional. Specifies that the command should only be executed while a condition is true. The command processing terminates as soon as the specified test evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

APPEND

Optional. Specifies that command output should be appended to the end of an existing file instead of overwriting the existing file. You must ensure that the structure of the two files is identical when using this parameter, if maintaining a consistent file structure is important (e.g., when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

ISOLOCALE locale_code

Optional. This parameter is only available in the Unicode edition of ACL. Specifies the locale. If you do not specify a locale, ACL uses the default locale. Locale codes are based on the ISO 639 standard language codes and the ISO 3166 standard country codes. Enter locale_code in the format language code_country code. For example, enter the code fr_ca for Canadian French. If you do not specify a country, ACL uses the default country for the language.

OPEN

Optional. Specifies that the table created by the command should be opened after the command executes. This parameter is only valid if the command creates an output table.

Remarks

The key fields in the primary and secondary tables must both be sorted in ascending order. If one or both key fields are unsorted, or sorted in descending order, ACL presents an error message and does not process the merge operation. You can use the PRESORT parameter to sort the primary key field. If the secondary key field is unsorted, you must first sort it in a separate sort operation before performing the merge.

The primary and secondary tables can be indexed instead of sorted. If you are merging two tables using an indexed common key field, omit the PRESORT parameter.

Only character fields, or character computed fields, can be key fields.

You can use the abbreviated MERGE ON key_fields syntax if the key fields in the two tables have the same name, and are fields, not expressions. Otherwise, you must use the PKEY/SKEY variation to identify each key field individually.

Examples

a. Merge tables with identical key field names

The following example merges two tables with identical key field names.

OPEN Employees_Location_1 PRIMARY
OPEN Employees_Location_2 SECONDARY
MERGE ON Last_Name TO "AllEmployees" PRESORT

b. Merge tables with different key field names

The following example merges two tables with different key field names.

OPEN Employees_Location_1 PRIMARY
OPEN Employees_Location_2 SECONDARY
MERGE PKEY Last_Name SKEY Surname TO "AllEmployees" PRESORT


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback