IBM ISERIES SC41-5210-04 - Manual (Page 110)

v Must be date constants if comparing to a date field. If the list contains correct representations of a date
but in other than SAA format, you may get unexpected results. To prevent unexpected results, the list
should be in SAA format.
v Must be time constants if comparing to a time field. If the list contains correct representations of a time
but in other than SAA format, you may get unexpected results. To prevent unexpected results, the list
should be in SAA format.
v Must be timestamp constants if comparing to a timestamp field.
Your list must contain at least two values, and they need not be in any particular order. You must enclose
each character constant in apostrophes and use two apostrophes where the character constant itself
contains an apostrophe (see the name
O’Grady in “Example 2: Testing for values in a list (LIST NLIST) in
Query for iSeries”).
If all of the values in the list do not fit on one line, just continue them on the next line under Value. If one
value ends in the last position of a line and you continue on the next line, you must put a blank space
before the value on the next line. You can split DBCS character constants by placing a DBCS shift-in
character in the last position of the first line and a DBCS shift-out character in the first position of the next
line.
Example 1: Testing for values in a list (LIST NLIST) in Query for iSeries: If you want to select only
records that have
04567, 00976, and 85432 in the ITEMNO field, you specify:
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, NLIST, LIKE, NLIKE, IS,ISNOT
AND/OR Field Test Value (Field, Number, or ’Characters’)
ITEMNO LIST 04567 00976 85432
____ ______________ _____ ______________________________
____ ______________ _____ ______________________________
Example 2: Testing for values in a list (LIST NLIST) in Query for iSeries: The following tests the field
LASTNAME for a number of different last names:
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, or ’Characters’)
LASTNAME LIST ’Staples’ ’Thorson’ ’Smith’ ’J
____ ______________ _____ ones’ ’Alison’ ’O’Grady’
____ ______________ _____ ______________________________
Testing for values that are similar (LIKE NLIKE) in Query for iSeries
You use the LIKE test to determine if a field has a pattern that is similar to the test pattern you specify.
You use the NLIKE test to determine if a field is not similar to the test pattern that you specify. You can
use LIKE comparisons for SBCS and DBCS character fields, but specifying a LIKE or NLIKE comparison
for any other field types causes an error message.
When you use a LIKE test, a record is selected if a certain part of the field has the same characters as the
test pattern you specify for the comparison value. For example, you would use the LIKE test if you wanted
to select all records that have a last name that begins with an
A.
You must enclose the test pattern in apostrophes; use two apostrophes where the test pattern itself
contains an apostrophe. If the test pattern consists of two apostrophes alone (
’), it is treated as an empty
string.
98 Query for iSeries Use V5R2