JOIN command

Used to combine fields from two ACL tables into a new ACL table.

Syntax

JOIN {PRIMARY|BOTH|SECONDARY|PRIMARY SECONDARY|UNMATCHED|MANY}
PKEY primary_key_fields FIELDS primary_fields SKEY secondary_key_fields
<WITH secondary_fields> <IF test> <WHILE test> <{FIRST|NEXT} range>
TO table_name <LOCAL> <OPEN> <APPEND> <PRESORT> <SECSORT>
<ISOLOCALE locale_code>

Parameters

PRIMARY

Includes matched records from the primary and secondary tables and unmatched records from the primary table in the output table. This parameter corresponds to the “Matched Primary Records” option in the Join dialog box, with the “Include All Primary Records” option selected.

BOTH

This parameter is equivalent to specifying PRIMARY.

SECONDARY

Includes matched records from the primary and secondary tables and unmatched records from the secondary table in the output table. This parameter corresponds to the “Matched Primary Records” option in the Join dialog box, with the “Include All Secondary Records” option selected.

PRIMARY SECONDARY

Includes all records from both the primary and secondary tables in the output table. This parameter corresponds to the “Matched Primary Records” option in the Join dialog box, with both the “Include All Primary Records” and “Include All Secondary Records” options selected.

UNMATCHED

Includes only unmatched records from the primary table in the output table. This parameter corresponds to the “Unmatched Primary Records” option in the Join dialog box.

MANY

Includes matched records from the primary table and matched records from the secondary table in the output table. If there is more than one key field match in the secondary table, ACL includes all of the matched records it finds. This parameter corresponds to the “Many-to-Many Matched Records” option in the Join dialog box.

PKEY primary_key_fields

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

FIELDS primary_fields

Specifies the fields or expressions from the primary table to include in the output.

SKEY secondary_key_fields

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

WITH secondary_fields

Specifies the fields or expressions from the secondary table to include in the output.

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.

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"

LOCAL

Optional. Specifies that the output file should be saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

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.

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.

PRESORT

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

SECSORT

Sorts the secondary table on the secondary key field before joining the tables. This option cannot be used in a group.

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.

Remarks

Omit the <PRIMARY|BOTH|SECONDARY|PRIMARY SECONDARY|UNMATCHED|MANY> parameter value after the JOIN command to include matched records from the Primary table only. Not specifying any of the join type parameter options corresponds to selecting the “Matched Primary Records” option in the Join dialog box.

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

Example

The following example joins the Ar and Customer tables using Customer Number (CustNo) as the common key field. The command uses the UNMATCHED parameter to create a new table with unmatched primary records, which results in a list of Ar records that are not associated with any Customer record.

OPEN Ar PRIMARY
OPEN Customer SECONDARY
JOIN PKEY CustNo FIELDS CustNo Due Amount SKEY CustNo UNMATCHED
TO "CustomerNotFound.fil" OPEN PRESORT SECSORT


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