Synergex.SynergyDE.Select.On

WTSupported in traditional Synergy on Windows
WNSupported in Synergy .NET on Windows
USupported on UNIX
VSupported on OpenVMS
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.).

Important

Once created, any object used by the Select (From, Where, NoCaseWhere, On, OrderBy, 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, On, OrderBy, 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.

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 and NoCaseWhere.

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.

Note

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.

See also

Joining data from multiple sources.

Examples

The following is an example of a join condition with a filter condition where OrderID is a key in the OrderDetails file.

(On)(Orders.OrderID == OrderDetails.OrderID && 
&    Orders.OrderDate > datetime)

The example below shows a join condition with a tag where both OrderID and Tag make up the join key.

record orderdetails
  group odkey
    Tag, a1
    OrderID, d10
  endgroup
  .
  .
  .
(On)(Orders.OrderID == OrderDetails.OrderID && OrderDetails.Tag == "H" &&
&    Orders.OrderDate > datetime)