IBM SC41-5210-04 manual Using more than one comparison test in Query for iSeries, Lastname

If the test pattern consists of two apostrophes alone (’’ or G’’) or two apostrophes enclosing only DBCS shift-out and shift-in characters (’s/os/i’ or G’s/os/i’), the test pattern is treated as an empty string. These patterns will select records in which the field contains an empty string.

Using more than one comparison test in Query for iSeries

When you use more than one comparison, you need to tell Query how to connect them by typing a connection in the AND/OR column. The two types of connections are:

vAND connections

vOR connections

If you do not specify anything in the AND/OR column between comparisons, Query assumes AND. All of the comparisons that are connected by ANDs must be true for the record to be selected by that group of comparisons.

For example, to select records for all customers with the last name of ’Clarke’ and who live in Arizona (‘AZ’) requires an AND connection:

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	EQ	’Clarke’
AND	STATE	EQ	’AZ’
____	______________	_____	______________________________
____	______________	_____	______________________________
____	______________	_____	______________________________
____	______________	_____	______________________________
			Bottom

________________________________________________________________________

ACCTNUMBER	STREETADDR	CRLIMIT	YEAR
COMPANY	CITY	BALDUE	MONTH
FIRSTNAME	STATE	PASTDUE
MIDDLEINIT	ZIPCODE	DATEPAID
LASTNAME	TELENUMBER	CURRENTDAT
			Bottom
F3=Exit	F5=Report	F9=Insert	F11=Display text
F12=Cancel	F13=Layout	F20=Reorganize	F24=More keys

Although this example shows the AND typed in the AND/OR column, it is not necessary. (If you leave the AND/OR column blank, Query assumes an AND connection.) Comparisons connected with one or more ANDs are considered a group of comparisons.

The other kind of connection between comparisons is OR; that is, one or more of the comparisons (or groups of comparisons) must be true for the record to be selected.

Because Query assumes AND connections unless told otherwise, you must specify OR in the AND/OR column whenever you want an OR connection.

For example, to select records in which the last name is Kingsbury or the credit limit is greater than or equal to $5,000 requires an OR connection:

Chapter 7. Selecting records in Query for iSeries 101