Synergex.SynergyDE.Select.On

Provide Join condition and additional filter criteria

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.

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.