WHERE

This item is optional. It specifies one or more conditions that records to be transferred must satisfy.

To transfer summary records, use this item to specify which records are to be grouped, then group the records. Using this item, you can specify one or more conditions that the record must satisfy to belong to a certain group. When WHERE is not specified, all records are grouped.

As the conditions, specify the test to be applied to the records in the specified file member. All the records in the specified file member are tested for the conditions specified here. Only those records that pass this test are transferred.

When WHERE is not specified, all records in the specified file member are transferred.

The condition format is as follows:

field-name  test  value

field-name

This must be a field substring or field name defined in the record format.

Fields or constants can be manipulated by specifying a supported function, with the results being used for comparison. The supported functions and usage are as follows:

SUBSTR: Returns the specified part of a character string. This function contains three parameters: the field name, starting position, and length of the returned substring. The following example returns the 20 characters starting from the 10th character of the FULLNAME field:
SUBSTR(FULLNAME 10 20)
VALUE: Returns the first non-null value in the parameter list. (If all parameters are null, null is returned.)
VALUE(DEPOSIT WITHDRAW BALANCE)
CURRENT: Returns DATE, TIME, TIMEZONE, or TIMESTAMP for the current system.
CURRENT(TIMEZONE)
DIGITS: Returns a character string representation of a numeric field.
DIGITS(EMPLOYEE#)
CHAR: Returns a character string representation of the date field, time field, or time-stamp field. The second parameter is used to specify the format of the Systems Application Architecture® (SAA) of the string to be returned (supported values are USA, EUR, ISO, or JIS).
CHAR(DATEHIRE USA)
DATE: Returns the date of the time-stamp field.
DATE(TIMECRTD)
TIME: Returns the time of the time-stamp field.
TIME(TIMECRTD)
TIMESTAMP: Returns the time-stamp, combining the date field and time field.
TIMESTAMP(DATESEND TIMESEND)
YEAR: Returns the year of the date field or time-stamp field.
YEAR(DATEHIRE)
MONTH: Returns the month of the date field or time-stamp field.
MONTH(DATEHIRE)
DAY: Returns the date of the date field or time-stamp field.
DAY(DATEHIRE)
DAYS: Returns the day of the year, counted from January 1, of the date field or time-stamp field.
DAYS(DATEHIRE)
HOUR: Returns the time of the time field or time-stamp field.
HOUR(TIMESEND)
MINUTE: Returns the minute of the time field or time-stamp field.
MINUTE(TIMESEND)
SECOND: Returns the second of the time field or time-stamp field.
SECOND(TIMESEND)
MICROSECOND: Returns the microsecond of the time field or time-stamp field.
MICROSECOND(TIMECRTD)

test

This is the comparison type to be applied to fields or functions.

The following tests can be used. One or more blanks can be placed before and after these tests.

Note: Values are searched according to the exact characters specified by the user. In other words, when the user's specification consists only of uppercase characters, only uppercase character strings are returned. Similarly, when the specification consists only lowercase characters, only lowercase character strings are returned.

=: Equal
<> or ><: Not equal
>: Greater than
>=: Greater than or equal to
<: Less than
<=: Less than or equal to
LIKE: The field is similar to the specified value.
BETWEEN: The field is equal to one of two constants, or to a value between them.
IN: The field is the same as one of the values in the constant list.
IS: The field contains null values.
ISNOT: The field contains no null values.

Test usage is as follows:

Using the LIKE Test

The LIKE test checks the field specified with the field name for a character pattern specified as a value. The field to be specified must be a character field.

The values to be tested must be character-string constants. This string can contain any characters. A percent (%) character indicates a character string consisting of zero or more characters. A 1-byte underscore (_) character indicates any single 1-byte character. A 2-byte underscore (_) character indicates any single 2-byte character.

The following example explains how to use the LIKE test:

NAME LIKE '%ANNE%'

The previous example searches for names containing character string ANNE, such as ANNE, ANNETTE, and SUZANNE.

The following example searches for names beginning with character string ANNE, such as ANNE and ANNETTE.

NAME LIKE 'ANNE%'

The following example searches for names ending with character string ANNE, such as ANNE and SUZANNE.

NAME LIKE '%ANNE'

The following example searches for all names whose second character is A.

NAME LIKE '_A%'

The following example searches for all last names beginning with character J.

LSTNAM LIKE 'J%'

This has the same effect as the following example:

SUBSTR (LSTNAM,1,1) = 'J'

When the pattern does not include a percent character (%), the length of the character string must be identical to that of the field.

Using the BETWEEN Test

The BETWEEN test checks the fields specified in the field name for character strings or numeric values that are equal to or between the specified constants. The values to be tested must be two character-string constants or two numeric constants. The types of these constants must be identical to that of the field name specified by the user. Delimit the two constants with AND.

The following example searches for those records for which the price is between 50.35 and 75.3, inclusive:

PRICE BETWEEN 50.35 AND 75.3

The following example searches for those records for which the name begins with C:

NAME BETWEEN 'C' AND 'CZZZZZZZZZ'

The following example searches for those records for which the balance is between 0 and 5␠000.

BALDUE BETWEEN 0 AND 5000

This has the same meaning as the following expression.

BALDUE >= 0 AND BALDUE <= 5000

Note: Specify the values to be tested in the form of BETWEEN (minimum) AND (maximum). For instance, BETWEEN 1 AND 10 is a valid specification. However, BETWEEN 10 AND 1 returns no records.

Using the IN Test

The IN test checks the fields specified in the field name for the character strings or numeric values in the list specified as the value. The value to be tested must be a list of character-string constants or numeric constants. In addition, the types of these constants must be identical to that of the specified field. Delimit the constants with blanks and enclose them in parentheses. Up to 100 constants can be specified. The following example shows how to use the IN test:

NAME IN ('SMITH' 'JONES' 'ANDERSON')

This example searches for those records for which the name is SMITH, JONES, or ANDERSON.

The following example searches for the values in the STATE field for which the value is other than NY, MN, or TX:

NOT STATE IN ('NY' 'MN' 'TX')

Note: Values are searched according to the exact characters specified by the user. In other words, when the user's specification consists of only uppercase characters, only uppercase character strings are returned. Similarly, when the specification consists of only lowercase characters, only lowercase character strings are returned.

Using the IS Test

The IS test checks the fields specified in the field name for null values.

The following example searches for those records for which the commission field contains null values:

COMMISSIONS IS NULL

Using the ISNOT Test

The ISNOT test checks the fields specified in the field name for non-null values.

The following example searches for those records for which the commission field does not contain null values:

COMMISSIONS ISNOT NULL

In the test, logical AND and logical OR can be combined. When both AND and OR are specified, AND comparison is performed first. Up to 50 conditions can be specified. For example:

MONTH=2 AND LOC='MIAMI' OR LOC='CHICAGO'

In this example, each record to be selected must satisfy the following condition:

MONTH=2 AND LOC='MIAMI'

or must satisfy the following condition:

LOC='CHICAGO'

This command can be modified by using parentheses. For example:

MONTH=2 AND (LOC='MIAMI' OR LOC='CHICAGO')

In this example, each record to be selected must satisfy the following condition:

MONTH=2

and it must satisfy the following condition:

LOC='MIAMI' OR LOC='CHICAGO'

NOT can also be used. The following example selects items where data is transferred not only from those records in which the DEPT field is not equal to 470, but also from those records for which the DEPT field is equal to 470 and, additionally, STATE is equal to NY.

NOT (DEPT = 470) OR (DEPT = 470 AND
  STATE = 'NY')

Comparison can start from a certain line and end at the next line. However, a field name cannot start from a certain line and end at the next line. Field names must not exceed one line.

When a value to be tested is a character string enclosed in quotation marks, the value can start from a certain line and continue to the next line.