IBM SC41-5210-04 manual Digits Query for iSeries function, Value Query for iSeries function

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:

DIGITS ( expression )

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:

v5, if the argument is a small binary value with no decimal positions.

v10, if the argument is a large binary value with no decimal positions.

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

62Query for iSeries Use V5R2