Where

Is

SELECT

A required literal.

filteritems

One of the following:

• * or the literal ALL to select all the data items in the data set. Note that if the SELECT statement specifies * or ALL for the item list of a variable format data set and no rectypenumber lists, then each variable format will return all items belonging to that format part. If the SELECT statement specifies a data item list without a rectypenumber, then that list applies to every variable format part.
• One or more data items from a fixed-format data set, as follows:

   dataitem[,] dataitem[,] dataitem

• One or more data items from a variable-format data set, as follows:

   rectypenumber: dataitem[,] dataitem[,] dataitem[,]
   rectypenumber: *
   rectypenumber: ALL
   [ ELSE: elseoption ]

Note: The elseoption can be NONE, * , or ALL. If the elseoption is NONE, unlisted record types will not be in the dataset enumeration and no data records of those types will be sent to the accessory. If elseoption is * or ALL, unlisted record types will be in the dataset enumeration, and records that satisfy the WHERE clause will be sent to the accessory. If the ELSE clause is not specified, it defaults to the filter’s DEFAULT option value.

FROM

A literal.

datasetname

The name of the data set or remap from which you want to filter records.

WHERE

A literal.

expression

One of the following:

• A data item from the data set followed by a relational operator and a value, as in the following examples:

  dataitem = value
  WHERE dataitem = n;
  WHERE BANK-ID = 1;
  WHERE BRANCH-ID = 2 AND BRANCH-NAME = "SOUTH MANHATTAN";

• A BOOLEAN data item, as in the following examples:

  WHERE EMP-SALARIED;
  WHERE EMP-GENDER OR EMP-ACTIVE;

• A type of update—CREATE, MODIFY or DELETE. Specifying an update type is useful for data warehousing, where sites don’t want to delete records. For example:

  WHERE UPDATETYPE (Modify);
  WHERE DI-ORD-PLANT = 23 and not UpdateType (Delete);

• An ALTERed data item. Specify the original attributes for the ALTERed data item. For example, if a data item was ALTERed from a NUMBER to an ALPHA, use the original numeric value in the WHERE clause, as follows:

  WHERE dataitem = numericvalue

• The DIV (integer division) or MOD (remainder) operators. This capability can be used to select representative sample records for testing purposes. For example:

  WHERE CUST-ID MOD 5 = 1

Note: expression can also use the literal CONTAINS, as in the following syntax:

dataitem CONTAINS "string"
WHERE alphadataitem CONTAINS "casesensitivestring"

The CONTAINS expression is true if the alpha data item contains the case-sensitive string enclosed in double quotation marks. You must include the quotation marks, as in the following example:

WHERE ADDR-LINE1 CONTAINS "BOX" OR ADDR-LINE1 CONTAINS "RURAL ROUTE"

USING

A literal.

set_or_subset

The name of an existing set or subset that points to the data set. DBEngine uses this during an extract to locate records in the data set, which means records will be extracted in the order specified by the key. You can decrease the time is takes to do the extract by specifying a subset that has relatively few records.

If set_or_subset is the name of an automatic subset, the WHERE clause defined in the DASDL for the subset will be appended to expression. Therefore, the accessory will receive only the records that satisfy both the subset and the WHERE clauses.

If set_or_subset is the name of a set or manual subset, there are no changes to expression.

If set_or_subset is the name of a manual subset, you must ensure that the manual subset actually contains all of the records satisfying the WHERE clause. DBEngine will not extract any records unless they are in the manual subset, but it will retrieve updates during the tracking phase for all records satisfying the WHERE clause, whether or not they are in the manual subset.

If set_or_subset does not allow duplicates, it becomes the primary set. When DBEngine enumerates the sets of a data set, it includes the specified subset.