Synergex.SynergyDE.Select.On

namespace Synergex.SynergyDE.Select
public class On

The On class is primarily used to provide the join condition with optional additional filter criteria. It’s similar to the Where class. However, it’s important to note that On has two distinct parts: a join condition first, followed by an optional filter criteria. The two parts are separated by a logical and operator (&& or .AND.). On uses the same implicit key selection as Where, but if no keys match, the join won’t occur.

Once created, any object used by the Select (From, Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse) must remain in contact with the same record(s) specified in the From object(s) as well as fields referenced in the Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse objects that are contained in the record(s). In other words, you can’t create one of these objects and pass it to another routine unless that routine has direct access to the original record. If this is not the case, an InvalidOperationException or $ERR_INVOPER error (“Where operator requires at least one field reference”) will result when creating the Select object. On the other hand, all data references outside a From object’s record are copied during the object’s creation and are not required to remain in contact with the original data.

Methods

Keynum

public static Keynum(krf), @On

Explicitly choose a key in the join condition. Sometimes, a join condition may match several keys of the inner table and the join will pick one of those keys based on optimization criteria (Implicit key selection via optimization). There may be times when choosing one of the other matching keys is more desirable. Specifying Keynum() allows you to explicitly override which of the keys to use. If a valid key is passed to Keynum() but it is not one of the keys matched in the join condition, a SynJoinException error (“Inner table requires key reference”) will occur. If the join condition only matches a single key, there is no need to specify Keynum.

public static Like(afield, pattern), @On

Specifies selection criteria in which an alpha field in the record specified in the From class object contains a value that matches a pattern. See Like.

public static Like(afield, wexpr), @On

Specifies selection criteria in which an alpha field in the record specified in the From class object contains multiple specific values in the form of wexpr, which is a Where class object (@Where). See Like.

Join operators

The join operators are limited to equality (==, .EQ., .EQS.) and are equivalent to their Synergy counterparts. See Expressions for additional information about each operator.

Filter operators

The filter operators can be any of the operators or methods supported in the Where class (except Where.Keynum(), Where.Portable(), and any of the change tracking methods). For a list of operators and methods, see Synergex.SynergyDE.Select.Where.

Discussion

The On class defines how two files are to be joined via common fields. The join condition becomes the key to do index look-ups on the joined files, and the filter condition is then applied to discard non-matches.

The join condition is made up of one or more join operations. A join operation is a binary equality operation (A == B) containing fields from either side of the join. Additionally, a join operation can contain an additional literal “tag” for choosing the key and criteria of the joined file.

To be assembled into the join condition, each join operation or tag operation must reference a key or key segment of the joined file and make up all or part of the same key (contiguously from the beginning). Once the join condition is assembled, all other operations become filter conditions.

The first or leftmost operation must be a join operation; otherwise an “Inner table requires key reference” error ($ERR_JOINKEYREQ) will be generated. When using a “tag,” the “tag” operation must occur after the leading join operation, as shown in Examples below.