Home page  
Help >
DDL Command MATCH
Version 7.11
DDL Command MATCH

The MATCH Command

MATCH is like a second level FIND. Its purpose is to isolate records in the same collection that "belong to" or "match with" other records currently isolated in that collection. MATCH does not clear the target area before adding in its isolation result.

Notation standards Options Answers

Syntax:
MATCH/optn cx FROM SourceTable THRU SourceField [SourceField2] WITH TargetTable [ON TargetField [TargetField2]]

Options:
                N              -Force non-key MATCH; see Note (3)
                R              -Skip the tree-building step; see Note (7)
                T              -Force the tree-building step; see Note (7)
                X              -Suppress the command response
                Y              -Do not alter the command answer cells

Answers:
                Ctl.Ans1                 =Number of additional records isolated
                Ctl.Ans2                 =Total number of records in target area
                Ctl.Ans3                 =Zero

Notes:
(1) MATCH does not clear the target area before adding its contribution to the collection. Thus, it is necessary to perform an explicit CLEAR on the target collection unless it is specifically necessary to retain the previous contents of the target collection.

(2) FROM, THRU, and WITH are optional words allowed for readability.

(3) The TargetField should be a key in the TargetTable for optimum performance of MATCH. However, MATCH will also function with a non-key TargetField by reading the entire TargetTable to determine MATCHes. The performance of this mode is dependent on the size of the TargetTable.

The /N option will force a non-key MATCH, even though the TargetField is a key.

(4) The purpose of the ON clause is to allow TargetField to be different from SourceField. However, TargetField and SourceField must be the same data type and the same size.

(5) If two fields (after the THRU) are involved in the match then both of them should be a key in the target. If one of them is not, then the entire match reverts to a non-key match.

(6) When two fields are specified, a match occurs only when both fields match. When two fields are specified their aggregate length may not exceed 256 characters.

(7) The default behavior of MATCH contains two distinct steps. The first step reads all records of the source collection and creates a list of unique values. The second step performs a query on each of the resulting values.

The purpose of the first step is to eliminate searches of duplicate values in the second step. However, if it is known that the values in the source collection have few or no duplicates, the first step can be skipped by using the /R option.

If SourceField is a Primary Key in SourceTable, the /R option is automatically invoked. The /T option will override this behavior and cause the first step to be performed unconditionally.

(8) There is a difference between %answer and %answer2 if some of the additional records isolated were already present in the collection. In particular, if all of the additional records isolated were already there before the MATCH, then %answer will be zero, but that does not mean that the MATCH didn't find anything, it just means that it didn't add anything that wasn't already there.

MATCH Examples

Copyright © 2019 , WhamTech, Inc.  All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. Names may be trademarks of their respective owners.