Filter
Top Products
SBCS character
Timestamp
DBCS-either
DBCS-open
v Any field can be compared to the keyword NULL using the IS or ISNOT test to determine if a field value
is or is not null.
Note: When comparing a date, time, or timestamp field with an SBCS, DBCS-either, or DBCS-open
character field, use a character field for which each value can be recognized as an SAA formatted
date, time, or timestamp. If the character field contains a correct representation of a date, time, or
timestamp, but in other than an SAA format, you may get unexpected results. When you run a
query that uses a non-SAA format for date, time, or timestamp values in character fields, and query
has no setting for a non-SAA literal date value, use the CHGJOB command to make sure your job
date format and separator match the format and separator in the character field values.
Usually you specify only one value per comparison. The exceptions are discussed in “Comparison tests in
Query for iSeries” on page 95.
Fields as values in Query for iSeries
If you use a field as a value (that is, in the Value column on the display), the field can be any field from
the list in the lower part of the display.
An example of a situation where you might use a field as a value is if you want only the records—from a
customer master file—with a balance due (in a field named BALDUE) greater than their credit limit (in a field
named CRLIMIT). In this example, you are comparing a field (BALDUE) with a value which is also a field
(CRLIMIT).
The fields being compared do not have to be the same length, and numeric fields do not have to have the
same number of decimal positions.
Character constants as values in Query for iSeries
A character constant is simply characters enclosed in apostrophes (for example, ’xxx’). The apostrophes
are important, because Query assumes that any group of characters not enclosed by apostrophes is a
field name. In DBCS character constants, shift-out and shift-in characters must surround DBCS data within
the apostrophes. DBCS-graphic character constants must be preceded by an uppercase or lowercase G.
For example, if you want only the records for Jan Alison, you compare the NAME field with ’Jan Alison’.
Also, note that if you do not use the apostrophes, Query tells you: Only one value is allowed for test.
The characters in the constant can be any combination of letters, numbers, or special characters (such as
*, ?, #, $, @) that might be found in the tested field. You must use apostrophes around a character
constant, even if it contains only numbers. You must also type uppercase and lowercase letters exactly as
you want them. The special characters underscore (_) and percent (%) have certain meanings when used
in a LIKE test.
If the character constant you specify contains an apostrophe, you must enter it as two apostrophes, so
Query does not think it has reached the end of the constant. For example, if you are looking for records
that list the street address as Granger’s Circle, you type the constant as ’Granger’s Circle’. Query
counts two apostrophes within a constant as a single apostrophe.
Numeric constants as values in Query for iSeries
You must not enclose a numeric constant in apostrophes. A numeric constant can be from 1 to 31 digits
long.
Do not use a currency sign (to indicate a monetary value) or a thousands separator (to indicate thousands,
millions, and so on) within a numeric constant.
94 Query for iSeries Use V5R2