Synergex.SynergyDE.Select.Where

Class containing methods and operators for Select expression processing

WSupported on Windows
USupported on Unix
VSupported on OpenVMS
NSupported in Synergy .NET
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.

or

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)

In

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.

Like

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.

or

public static Like(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 like method, which returns a Where class object.

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.

Note

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.

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:

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:

Note

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:

Note

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:

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:

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

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:

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:

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.

See also

Examples

import Synergex.SynergyDE.Select
main
    .include "customers" repository, record="cust_rec"

proc
    begin
      data sobj, @select
      data fobj, @From
      data wobj, @Where
      data cust_chan,  i4, 0
      data out_chan,   i4, 0
      open(cust_chan,I:I,"DAT:customer")
      open(out_chan, O, "tt:")

      fobj = new From(cust_chan, cust_rec)
      wobj = (Where)cust_rec.cust_state.eqs."NV"
      ;wobj = Where.Contains(cust_state,"NV")
      sobj = new Select(fobj,wobj)
      ;;;OR
      ;sobj = new Select(new From(cust_chan,cust_rec),(Where)cust_rec.cust_state.eqs."NV")
      ;;OR if you don't want a case sensitive WHERE
      ;sobj = new Select(new From(cust_chan,cust_rec),(NoCaseWhere)cust_rec.cust_state.eqs."NV")
      foreach cust_rec in sobj
      begin
        writes(out_chan,cust_rec)
      end
      close cust_chan, out_chan
    end
endmain