Synergex.SynergyDE.Select.Where

Class containing methods and operators for Select expression processing

namespace Synergex.SynergyDE.Select
public class Where

The Where class object is the fundamental building block for a selection expression. Essentially a collection of logical and relational operators (including their symbolic forms), it provides a means to create intuitive, procedure-like expressions for record selection. Ideally, you will create a Where expression that is optimized for your data. If Where is not specified or has no record scrutiny, all records are selected.

A Where expression is processed from left to right. When an expression includes a key of reference, the Select attempts to optimize it. See Optimization and ordering for ISAM files for a full discussion of optimization.

A Where expression requires at least one operand of each operator to be a field defined within the record specified by the From object. In addition, one of those operands must also be explicitly or implicitly cast to a Where object. For example,

(Where)fld1.eq.1.and.(Where)fld2.gt.20

A special rule permitted only in a Where expression allows an explicit cast of a parenthesized expression that will apply implicit casts to each of the Where class operators in the expression. For example, with implied Where expression casting

(Where)fld1.eq.1.and.(Where)fld2.gt.20

can be simplified to

(Where)(fld1.eq.1.and.fld2.gt.20)

The methods in the Select.Where class are case-sensitive.

Methods

Between

public static Between(field, low, high), @Where

Specifies selection criteria in which the value of a field is between two specific values. This method returns a Where class object and takes as its parameters a single field specification, the low value for the field, and the high value for the field (all MISMATCH n). At runtime, it is transformed to the appropriate expression in the form field.ge.low.and.field.le.high.

Contains

public static Contains(afield, value), @Where

Specifies selection criteria in which an alpha field in the record specified in the From class object contains a specific value (a), which is a single alpha data reference. This method implements an SQL-like contains method, which returns a Where class object.

public static Contains(afield, wexpr), @Where

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). Wexpr must be an alpha expression constructed with one or more of the following binary operators (or their symbolic forms): AND, OR, NOT. The comparison is case-sensitive. This method implements an SQL-like contains method, which returns a Where class object.

A positive selection occurs when the alpha value is contained within the specified field. The specified field must be a field within the record specified in the From class object; otherwise an InvalidOperationException or $ERR_INVOPER error (“CONTAINS operator left operand requires field reference”) is generated at runtime. For example,

Where.Contains(field, avar) or
Where.Contains(field, (Where)avar1 .or. avar2)

public static varargs In(field, value1, value2, ...), @Where

Specifies selection criteria in which a field is one of the specified values in a list. This method returns a Where class object and takes as its parameters a single field specification followed by two or more value parameters (all MISMATCH n). At runtime, this method is transformed into the appropriate expression in the form field.eq.value1.or.field.eq.value2.or.field.eq.value3.

public static Like(afield, pattern), @Where

Specifies selection criteria in which an alpha field in the record specified in the From class object contains a value that matches a pattern (a), which is a single alpha data reference. This method implements an SQL-like like method, which returns a Where class object.

public static Like(afield, wexpr), @Where

A positive selection occurs when the alpha value matches the pattern in the specified field. The specified field must be a field within the record specified in the From class object; otherwise an InvalidOperationException or $ERR_INVOPER error (“LIKE operator left operand requires field reference”) is generated at runtime. For example,

Where.Like(field, avar) or
Where.Like(field, (Where)avar1 .or. avar2)

The pattern can include special pattern-matching wildcard characters:

Match	Wildcard	Example
Zero or more characters	%	bl% finds bl, black, blue, and blob
Single character	_	h_t finds hot, hat, and hit
Single character in a set	[ ]	h[oa]t finds hot and hat but not hit
Single character not in a set	[^]	h[^oa]t finds hit but not hot and hat
Single character in a range	[-]	c[a-c]t finds cat, cbt, and cct but not cot

Any wildcard character can be escaped by preceding it with a backslash (\). The backslash character can be escaped by preceding it with a backslash.

The Like method is also a member of the NoCaseWhere class. When used with the NoCaseWhere, the pattern matching is case insensitive.

For example,

Where.Like(city, "Sa%")

Matches a city that starts with “Sa”, such as “Sacramento,” “San Francisco,” or “San Diego.”

Where.Like(city, "Sa[cn]%")

Matches a city that starts with “Sac” or “San”.

Where.Like(field, "[h-k]%")

Matches a field that starts with h, i, j, or k.

Where.Like(city, "L_[ns]_on")

Matches a city that starts with “L” followed by any single character followed by “n” or “s” followed by any single character followed by the two characters “on”, such as “London” and “Lisbon.”

Keynum

public static Keynum(krf), @Where

Sets the explicit key of reference (n) for optimization and retrieval ordering.

If Keynum() is not specified in a Where expression, the primary key is used for optimization and ordering. See Optimization and ordering for ISAM files for more information.

Only one Keynum() can be specified in a Where expression, and it must be separated from the rest of the Where expression by the .AND. operator. For example,

Where.Keynum(1) .and. Where.Contains(field, value)

If the value passed to Keynum() does not match an existing KEYNUM for the file specified by the From object, an “Illegal key specified” error ($ERR_BADKEY) will be generated at runtime.

When either Keynum or Portable is specified, it must be the first (leftmost) operator in the Where expression. When both Keynum and Portable are specified, one must be the first operator and the other must be second.

Portable

public static varargs Portable(intfield), @Where

Identifies integer fields in a record that have been stored to the file as portable integers. Without identifying portable integers, integer fields used in a Where expression will be compared natively.

Typically an application that shares files between systems with a different endian byte order will convert non-key integers to portable format before storing and after reading records from the shared files. The Portable() method is provided for handling records read from files of this type. (Portable() only affects functions of Select; it does not affect STORE.)

Only one Portable() can be specified in a Where expression, and it must be separated from the rest of the Where expression by the .AND. operator. For example,

Where.Portable(ifld) .and. Where.Contains(field, value)

It is not necessary to identify fields used as integer keys (including autokeys) with the Portable() method, as they are already known.

Change tracking methods

See Tracking file changes in the Discussion for more information.

NetChange

public static method NetChange(snapshot1, shapshot2), @Where

Selects the net resulting changed records (deltas) between snapshot1 and snapshot2. (n)

Changes

public static method Changes(snapshot1, shapshot2), @Where

Selects all changed records (deltas) between snapshot1 and snapshot2. (n)

Snapshot

public static method Snapshot(snapshot), @Where

Selects all records in the file as they were at the time of the specified snapshot. (n)

ChangeType

public static method ChangeType(state), @Where

Selects all records that have changes that match state, which is a CTState enumeration. The CTState enumeration is returned by the CTInfo.GetCTState property. See Synergex.SynergyDE.Select.CTState. (CTState)

Operators

The Where operators are equivalent to their Synergy counterparts. Not all Synergy operators are supported in Where, but the following operators (in their various formats) are supported. See Expressions for additional information about these operators.

Equality (==, .EQ., .EQS.)
Inequality (!=, .NE., .NES.)
Greater than (>, .GT., .GTS.)
Greater than or equal to (>=, .GE., .GES.)
Less than (<, .LT., .LTS.)
Less than or equal to (<=, .LE., .LES.)
Logical and (&&, .AND.)
Logical not (!, .NOT.)
Logical or (||, .OR.)
Cast ((Where))

The string relational operators (.EQS., .GTS., .LTS., etc.) and their symbolic relational forms (==, >, <, etc.) always result in a string comparison, compared for both of their lengths. If the lengths differ, the shorter operand is logically extended with blanks on the right until it is the same size as the larger operand.

Note that in a Where expression, the symbolic operators (==, >=, etc.) are treated a little differently than they are in the Language:

Symbolic operator expression	Language expression equivalence	Where expression equivalence
(avar1 == avar2)	avar1 .eq. avar2	avar1 .eqs. avar2
(avar1 == string)	avar1 .eqs. string	avar1 .eqs. (a)string

SQL symbolic comparisons such as ==, >, <, etc., behave like string comparisons. For example,

(avar .eqs. "abc") is equivalent to the SQL statement (WHERE avar = 'abc')

and

(avar .eq. "abc") is equivalent to the SQL statement (WHERE avar LIKE 'abc*')

The alpha relational operators (.EQ., .GT., .LT., etc.) always result in an alpha comparison, compared for the length of the shortest. This is similar to the SQL LIKE operator with an implied trailing “%” wildcard.

Optimization and ordering for ISAM files

There are two distinct types of optimization, depending on whether an .OR. is present in the Where clause. If your Where clause contains an .OR., see Optimizing when an .OR. is present in the Where clause. All of the other sections below refer to cases in which no .OR. is present.

Explicit and implicit ordering

The ordering of retrieval can be specified explicitly or implicitly. The order of precedence is as follows:

Where.Keynum(n) and/or OrderBy.AscendingKey(n) or OrderBy.DescendingKey(n) explicitly sets the key of reference to the specified key, which determines the retrieval order.
If neither Where.Keynum() nor OrderBy(key) is specified, an optimized key determines the retrieval order.
If no optimization occurs, the primary key determines the retrieval order. This is effectively a table scan that, depending on the file size, may perform very slowly. On Windows and Unix, performance can be greatly improved by opening the file /nonkey_sequential, but the retrieval order will be determined by the record’s physical location in the data file, which may not be desirable. Additionally, OrderBy.Ascending(fld) and/or OrderBy.Descending(fld) can be used to alter that retrieval order. Both of these cases read directly from the data file (skipping the index) for high-speed retrieval.

Implicit key selection via optimization

If the key of reference is not explicitly specified using Where.Keynum() or OrderBy(key), key optimization will implicitly set the key that determines the retrieval order. If key optimization cannot be achieved, the primary key becomes the key of reference (unless the file was opened /nonkey_sequential).

In a Where (or On) clause, starting from left to right, fields that are contained in a key specified with Equality operators (EQ) then relational operators (GE, GT, LE, LT) are gathered, in order, into key segments. The key with the most key segments is chosen, based on the following order of precedence:

A key that does not allow duplicates and all key segments make up the entire length of the key determines the optimized key. When more than one of these exist, the one whose key segments match the order specified in the Where or On clause takes precedence.
A key with the most contiguous segments determines the optimized key. When more than one of these exist, the one whose key segments match the order specified in the Where or On clause takes precedence.
A key with the longest single key segment determines the optimized key.

The use of “key segment,” as it relates here to Where clause optimization, refers to the part of a key that matches a field specified in the Where clause. It doesn’t necessarily have to be an actual segmented key.

The following key selection is presented in order of precedence:

In the Where clause, starting from left to right, the first equality operator (EQ) whose field specification matches a key (or partial key starting from the beginning of the key) or key segment (beginning with the first segment) determines the optimized key.
In the Where clause, starting from left to right, the first relational operator narrowing pair (greater-than GT or GE with matching less-than LT or LE) whose field specification matches a key (or partial key starting from the beginning of the key) or key segment (beginning with the first segment) determines the optimized key.
In the Where clause, starting from left to right, the first relational operator (LT, LE, GT, or GE) whose field specification matches a key (or partial key starting from the beginning of the key) or key segment (beginning with the first segment) determines the optimized key.

To be eligible for optimization, the Where clause must not contain any .OR. or .NOT. operators.

Optimization types

There are currently two types of Where clause optimization: head optimization and tail optimization. Head optimization is a single direct positioning (e.g., FIND) to the beginning of the selection with little or no record I/O. In the absence of head optimization, sequential record I/O occurs starting from the beginning of the file. Tail optimization is a known exit condition that terminates the sequential record I/O when it is known that past a certain point there will be no further matches to the selection criteria. In the absence of tail optimization, sequential record I/O continues until the end of the file.

A key or segment contained in an EQ operator expression will always generate both head and tail optimization. A key or segment contained in a relational operator expression will generate either a head or a tail optimization, depending on the key or segment order. A key or segment contained in a pair of narrowing relational operator expressions will generate both head and tail optimization. (A narrowing condition is defined as two relational operator expressions on the same key or key segment where one is a GT or GE relational operator and the other is the complement relational operator LT or LE.) For example,

(Where)(fld1.ge.low.and.fld1.le.high)

To view the optimization types, you can use the Select.IsOptimized property. (See IsOptimized.)

Optimizing key segments

To optimize key segments, the following should be considered:

All key segments that will be optimized must be contiguous segments starting from segment 1.
All segments other than the first must be preceded by a prior segment contained in an EQ operator expression to be optimized.

A narrowing condition involving a segmented key is possible when two comparisons using the same key segment are complementary relational operators, provided the above two rules are true.

Ordering of segmented keys is determined by the following:

If individual segment order is not specified during file creation (ORDER=), the retrieved order is based on the entire key.
If the Where clause is not eligible for optimization, the retrieved order is based on the order of the first segment.
When optimized, the segment order of the first key segment with a relational operator that follows a prior segment or the last key segment containing an equality operator determines the retrieval order. For example, the order in this expression is based on segment 2 (seg2):

(Where)(seg1.eq.500.and.seq2.gt.10.and.seg3.lt.40)

To assist in better optimizing a Select, see DBG_SELECT.

Using key optimization (ISAM files)

To make use of Where clause optimization for best performance, consider the following:

When more than one record match is necessary (using an OR condition), consider using two separate Selects rather than using an OR. Use GE or LE instead of EQ .OR. GT, etc.
Avoid using .NOT. when possible. Use GE instead of .NOT. LT, etc.
Place fields that coincide with the most unique key leftmost in the Where clause to force the best optimization.
A key matching a field used in a NoCaseWhere clause must also be defined as a NOCASE key type in the ISAM file in order for the Select to choose that key for optimization.
When keys cannot be optimized, consider using OrderBy.Ascending() or OrderBy.Descending() when creating the Select Object. Also, if retrieval order doesn’t matter, consider opening the file /nonkey_sequential. Either of these will greatly improve overall performance when there’s no key optimization.
OrderBy.Ascending() and OrderBy.Descending() attempt to optimize to a matching existing key with the same or reverse order. (On OpenVMS, NODUPS must be set.) To match a key with multiple segments, the order of all key segments must be the same. An OrderBy that matches the same key optimized by the Where whose order matches the OrderBy will suppress the ordering sort.

Optimizing when an .OR. is present in the Where clause

Optimization works differently when a Where clause contains an .OR.. If the Where clause does not contain an .OR, see the other sections above, starting with Explicit and implicit ordering.

Where.In(field, value1, value2[, ...]) is expanded into a series of EQ/EQS comparisons separated by .OR.s, like this:

((field .EQ. value1) .OR. (field .EQ. value2) .OR. (field .EQ. value3))

The Where expression is analyzed to determine if it is optimizable. The field must be a key field for the expression to be optimizable. If the series of .OR.s are separated from the rest of the expression by another .OR., the expression is not optimizable. The key field cannot be a case-insensitive key. If the key is segmented, the field must start with the first segment.

If the expression contains an additional AND condition, such as

Where.In(field1, value1, value2) .and. (Where)(field2 .eq. value3)

each comparsion of value1 and value2 includes the AND part of the expression, making separate passes for each IN parameter specified. It is expanded like this:

((field .EQ. value1) .and. (field2 .EQ. value3)) .or. ((field .EQ. value2) .and. (field2 .EQ. value3))

Keynum(n) can be used to explicitly specify which key field to use for optimization.

Tracking file changes

The NetChange(), Changes(), and ChangeType() methods enable you to access earlier versions of records in a file, while Snapshot() allows you to access an earlier version of the file. Note that you must first enable change tracking using the ctutl utility, and your ISAM files must contain change tracking snapshots. (See ctutl for more information.) Common uses for change tracking information include programmatically accessing a file from a previous state or improving record processing at the end of a period.

A snapshot number (as specified in a NetChange(), Changes(), or Snapshot() method) can be absolute or relative:

An absolute snapshot number is a positive number that corresponds to a physical snapshot.
A relative snapshot number is a negative number that is applied either to the current snapshot ID or to an absolute snapshot number, and the result corresponds to a physical snapshot.

For example, Where.Changes(20,-1) is equivalent to Where.Changes(20,19).

The table below shows the results for various combinations of snapshot specifications (as used with the NetChange() and Changes() methods).

Dual snapshot specification	Result
Two absolute snapshot numbers	Selects change records between the lowest (oldest) snapshot number and the highest (newest) snapshot number.
An absolute snapshot number and zero	Selects change records starting from the lowest (oldest) snapshot up to the specified snapshot number.
A relative snapshot number and zero	Calculates the lowest (oldest) snapshot by subtracting the specified relative number from the current snapshot ID and selects change records up to the current time.
A relative snapshot number and an absolute snapshot number	Calculates the lowest (oldest) snapshot by subtracting the relative number from the absolute number and selects change records between those two snapshots.
Snapshot numbers of the same value	Selects change records made between the specified snapshot and the previous snapshot.
Two relative snapshot numbers	Calculates the lowest (oldest) and highest (newest) snapshot by subtracting them from the current snapshot ID and selects change records between those two snapshots.

When specifying a snapshot range, the entries selected will start from the snapshot following the lowest (oldest) snapshot through those of the highest (newest) snapshot. Thus, (2,3) will select all changes in snapshot 3 but none in snapshot 2, while (3,5) will include all of snapshot 4 and 5 but none in snapshot 3.

To access all changed records between two snapshot numbers, you’d use the Select class as follows:

foreach rec in new Select(from, Where.Changes(snapshot1, snapshot2))
   begin
       ; Processing
   end

To add application code to locate all net changes made between two snapshots, use the Select class as follows:

foreach rec in new Select(from, Where.Netchange(snapshot1, snapshot2))
    begin
      type = Select.GetEnum().GetCTInfo.CTState
      ; Processing
    end

Following the selection, the change tracking status is identified using the GetCTInfo property.

The table below shows the results for a single snapshot specification (as used with the Snapshot() method).

Single snapshot specification	Result
Absolute snapshot number	Selects all records in a file (including untracked) as they were as of the specified snapshot.
A relative snapshot number	Calculates the snapshot relative to the current time. (-1 means the most recent snapshot.)
0	Calculates the snapshot as the beginning snapshot (will only contain untracked records).

To access a file’s records as they were at a particular point in time (a snapshot) in no particular order, you’d use the Select class as follows. (Note that the entire file as of that snapshot will be selected, including records deleted after that snapshot.)

foreach rec in new Select(from, Where.Snapshot(snapshot))
  begin
    ; Processing 
  end

Alternatively, to access those records in order, you can use Select.OrderBy like this:

foreach rec in new Select(from, Where.Snapshot(n),OrderBy.Ascending(key))
  begin
    ; Processing
  end

If you only wanted to select records that were added during the last recorded snapshot window, your FOREACH statement would look something like this:

foreach rec in new Select(from, Where.Changes(-1, -2) .and.
  &     Where.ChangeType(CTState.Insert))

To select records that were added since the last recorded snapshot, use

foreach rec in new Select(from, Where.Changes(0, -1) .and. 
  &     Where.ChangeType(CTState.Insert))

Also see Change tracking for more information.