DUPLICATES command

Used to detect whether duplicate values or entire duplicate records exist in an ACL table.

Syntax

DUPLICATES <ON> key_fields <D> <OTHER fields> <UNFORMATTED>
<TO SCREEN|table_name|filename|PRINT> <APPEND> <IF test> <WHILE test>
<FIRST|NEXT range> <HEADER header_text> <FOOTER footer_text> <PRESORT>
<OPEN> <LOCAL> <ISOLOCALE locale_code>

Parameters

ON key_fields

Specifies the fields or expressions to check for duplicates. The ON parameter can be omitted.

D

Optional. Specifies that key fields should be sorted in descending order. The default sort order is ascending.

OTHER fields

Specifies a list of fields or expressions to include in the output for a duplicates test. The fields are reported in columnar format with duplicate items displayed in grouped rows. A blank line separates each set of duplicates. The duplicated key field values are reported in the first column, followed by the specified fields.

UNFORMATTED

Suppresses page headings and page breaks when the results are output to a file.

TO SCREEN | table_name | filename | PRINT
  • Specify TO SCREEN to display the results in the ACL display area. This is the default output if the TO parameter is not specified.

  • 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"

  • Specify TO filename to write the results to a file. You must specify the filename value as a quoted string with the appropriate file extension. For example, specify the .TXT file extension to create a text file.

    For example: TO "Output.TXT"

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

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

  • Specify TO PRINT to send the results to the default printer.

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.

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.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

PRESORT

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

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.

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.

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.

ACL output variables

GAPDUPn

Stores the total number of duplicates identified by the command.

Example

The following example identifies duplicate values in the Count field and outputs the results to a new ACL table.

DUPLICATES ON Count OTHER ProdCls PRESORT OPEN TO "Duplicates.FIL"

Related reference
FUZZYDUP command


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