Example of a character field substring: If a character field named
ALPHA
containing the value
ABCDEFGHI
is
used in
SUBSTR(ALPHA,4,3)
, the result is a character field containing
DEF
. If you do not specify a value for
Length
, the result is
DEFGHI
.
If the
Offset
and
Length
values cause the substring to exceed the right end of the field, you will receive an
error message. If any argument can be null, the result field can be null. If any argument is null, the result
is null.
If a variable-length field is used for
Value
, the result is a variable-length field. If either
Offset
or
Length
is
an expression, the result is a variable-length field.
For SBCS, DBCS-open, DBCS-only, and DBCS-either fields,
Offset
and
Length
refer to bytes, including
shift-out and shift-in characters. For example, if FIELD1 contains string
<A1B1C1D1E1F1>
, the operation
SUBSTR(FIELD1,2,3)
results in a character field containing
A1B
.
For DBCS-graphic fields,
Offset
and
Length
refer to the number of double-byte characters. Shift-out and
shift-in characters in a graphic constant are ignored. For example, the operation
SUBSTR(G’<A1B1C1D1E1F1>’,2,3)
results in a graphic field containing
B1C1D1
.
DIGITS Query for iSeries function
The DIGITS function returns a character representation of a number. The form is:
QQ DIGITS (
expression
)
QR
The argument must be an integer or decimal value. The result of the function is a fixed-length character
string. The CCSID of the string is the default SBCS CCSID at the application server. If the argument can
be null, the result can be null. If the argument is null, the result is a null value.
The result is a string of digits that represents the absolute value of the argument without regard to its
scale. The result does not include a sign or a decimal point. The result includes any necessary leading
zeros so that the length of the string is:
v
5, if the argument is a small binary value with no decimal positions.
v
10, if the argument is a large binary value with no decimal positions.
v
The length of the argument, if the value is a packed, zoned, or binary field with decimal positions.
Example:
DIGITS(JOBCODE)
VALUE Query for iSeries function
The VALUE function can be used in any type of expression: character, numeric, date, time, or timestamp.
The VALUE function, VALUE(x,y), returns the first argument that is not null. The arguments are evaluated
in the order in which they are specified. The arguments must be compatible; character string arguments
are not compatible with numbers. X is a field and Y can be a field, value, or a list of fields or values. X can
be any data type and may be a previously defined result field or any file field.
The result can be null only if all arguments can be null; the result is null only if all arguments are null. The
X value is not checked to determine if it is null-capable.
Example:
VALUE(commission, 0)
If commission is null, the result is 0.
62
Query for iSeries Use V5R2
Содержание ISERIES SC41-5210-04
Страница 1: ...iSeries Query for iSeries Use Version 5 SC41 5210 04 ERserver ...
Страница 2: ......
Страница 3: ...iSeries Query for iSeries Use Version 5 SC41 5210 04 ERserver ...
Страница 12: ...x Query for iSeries Use V5R2 ...
Страница 14: ...2 Query for iSeries Use V5R2 ...
Страница 18: ...Figure 3 Major Tasks on the Query Menu and the Work with Queries Display 6 Query for iSeries Use V5R2 ...
Страница 32: ...20 Query for iSeries Use V5R2 ...
Страница 38: ...26 Query for iSeries Use V5R2 ...
Страница 44: ...32 Query for iSeries Use V5R2 ...
Страница 116: ...104 Query for iSeries Use V5R2 ...
Страница 122: ...110 Query for iSeries Use V5R2 ...
Страница 146: ...134 Query for iSeries Use V5R2 ...
Страница 150: ...138 Query for iSeries Use V5R2 ...
Страница 174: ...162 Query for iSeries Use V5R2 ...
Страница 178: ...166 Query for iSeries Use V5R2 ...
Страница 196: ...184 Query for iSeries Use V5R2 ...
Страница 198: ...186 Query for iSeries Use V5R2 ...
Страница 204: ...192 Query for iSeries Use V5R2 ...
Страница 205: ...Part 4 Appendixes Copyright IBM Corp 2000 2002 193 ...
Страница 206: ...194 Query for iSeries Use V5R2 ...
Страница 210: ...198 Query for iSeries Use V5R2 ...
Страница 242: ...230 Query for iSeries Use V5R2 ...
Страница 256: ...244 Query for iSeries Use V5R2 ...
Страница 270: ...258 Query for iSeries Use V5R2 ...
Страница 272: ...260 Query for iSeries Use V5R2 ...
Страница 292: ...280 Query for iSeries Use V5R2 ...
Страница 293: ......
Страница 294: ... Printed in U S A SC41 5210 04 ...