IBM SC41-5210-04 manual Fields as values in Query for iSeries

SBCS character

Timestamp

DBCS-either

DBCS-open

vAny 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.

94Query for iSeries Use V5R2