WHERE keyword

The WHERE keyword provides the capability to specify conditional expressions that determine which segments are selected for processing. WHERE can be specified with each SELECT primary command object keyword.

Usage Notes

1. Values that include spaces or the following special characters must be delimited by single quotation marks.

   =    Equal            +    Plus
        -    Minus            |    Vertical Bar
        >    Greater than     <    Less than
        (    Left paren       )    Right paren
        .    Period           /    Slash
        :    Colon            ;    Semi-Colon
        &    Ampersand        *    Asterisk
        !    Exclamation Mark 

   If a value contains an embedded quotation mark, use three adjacent quotation marks for each embedded one. For example,
     JOHN’S NAME
   would be entered as
     JOHN’’’S NAME

2. Segments that meet the WHERE selection criteria are bypassed until the start and skip frequencies are satisfied. The remaining segments are then processed until the maximum indicated is reached.

3. The conditional expression following the WHERE keyword must begin with a valid field name followed by a subscript when applicable, a relational operator, and a literal value or variable input assignment. The expression must also supply the record type value (RTV) for segments that use multiple layouts. For more information of processing segments with multiple layouts, see Segments with Multiple Layouts. The field name is one of the following:

   • A COBOL or PL/I layout name or a DBD field name.
   • $RTVn where n=1 for the first record type value or 2 for the second.

   Following is an example of a simple expression:

   SELECT SEGMENT A
   WHERE FLDA-KEY=A10327 AND $RTV1=$ALL;

4. Conditional expressions can be combined using logical connectors to form complex expressions. Following is an example of a complex expression:

   SELECT SEGMENT A
   WHERE FLDA-01(2)>=1985 AND FLDA-01(2)<=1990 AND $RTV1=PO;
5. A field value can be specified with a literal or an input file variable. For more information, see  Step 5.
6. The end of a conditional expression is determined by another optional keyword, an object keyword, or a sub-command.

7. Relational Operators:

   Operator	Description
   EQ or =	Equal to
   NE or Ø = or =Ø	Not equal to
   GT or >	Greater than
   LT or <	Less than
   GE or > = or =>	Greater than or equal to
   LE or < = or =<	Less than or equal to
   BT	Between; within a range of two values (endpoints inclusive)
   NB	Not between; outside a range of two values (endpoints exclusive)
   CO	Contains; scans for the presence of the data value.
   NC	Not contains; scans for the absence of the data value.

8. Logical Connectors:

   Connector	Description
   AND or &	Logical AND
   OR or |	Logical OR

9. When using a PL/I layout with a data type of BIT, WHERE is limited to the first 16 bits of a bit string. Externally, each bit of the field referenced by the WHERE is represented by a character 0 or a character 1.
10. IX_INVALID 10. Select segments with an invalid field value by entering the word INVALID. Use the word INVALID for alphanumeric, binary, packed decimal, and zoned decimal fields only. For numeric fields, any value that does not conform to the field’s data type is invalid. For alphanumeric fields, any value that contains non-displayable data is invalid.
11. Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, AND is applied before OR.
12. 1 The BT relational operator includes the endpoints of the range you specify. The NB relational operator does not include the endpoints. The BT and NB relational operators are not valid for Double-Byte Character Set (DBCS) support.
13. 1 The CO and NC relational operators can be specified only for alphanumeric fields (PIC X or PIC 9 for COBOL, CHAR, PIC X, or PIC 9 for PL/I).

Examples

The following SELECT command statement defines the criteria for a specific customer using the WHERE keyword. If the customer is found, the customer record is printed in an unformatted mode.

The following command statements define the criteria for all customers in sales zone 3 and region 18 with a sales representative of 539. Because OPTION=PATH is the default for PRINT, the CUSTOMER segment is printed only if both the customer and sales representative segments are satisfied. If OPTION=NOPATH is specified, only the customers without sales representative 539 are printed.

The following command statements define the criteria using a WHERE keyword and an input file to select a list of specific customers for printing. All customer segments that satisfy the selection criteria are printed.

The following command statements define the criteria to find all CUSTOMER segments with CUST-STATUS fields that are INVALID and change them to CUST-STATUS=A.

The following command statements define the criteria to find all CUSTOMER segments with CUST-NUMBER 10357 and CUST-STATUS A, B, or C.

The following command statements define the criteria to find all CUSTOMER segments with CUST-NUMBER between 10000 and 20000, including endpoints 10000 and 20000.