IBM @server iSeries, User Manual

Page: 74 / 294

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