IBM SC41-5210-04 manual Varchar Query for iSeries function

Note: If you are sending queries between countries that use the comma for a decimal point, put a blank after each comma separating values in a list of numeric values.

The selected argument is converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:

vIf the arguments are dates, the result is a date. If the arguments are times, the result is a time. If the arguments are timestamps, the result is a timestamp.

vIf the arguments are constants, the CCSID of the result is the CCSID that would result if the arguments were concatenated.

vIf all arguments are fixed-length, the result is a fixed length of n, where n is the length of the longest argument.

vIf any argument is variable length, the result is variable-length with length attribute n, where n is the length attribute of the argument with the greatest length attribute.

vIf the arguments are numbers, the data type of the result is the data type that would result if the arguments were added.

vIf all arguments are DBCS-only, the result is DBCS-only.

vIf the arguments are any combination of bracketed-DBCS, the result is DBCS-open.

vIf the arguments are DBCS-graphic, the result is DBCS-graphic.

If X is a DBCS-graphic field, Y fields and constants must all be DBCS-graphic. If X is not a DBCS-graphic field, no Y fields or constants may be DBCS-graphic.

The CCSID of the result is as follows:

vCCSID 65535 if any field or constant has CCSID 65535.

vMixed CCSID if any field has a mixed CCSID.

vCCSID of a file field has precedence over result fields or constants.

vCCSID of result fields has precedence over constants.

vCCSID assigned to the result will be the CCSID of the first of the two values if both concatenated values are fields from the file, or both are result fields.

vCCSID 65535 is assigned to a DBCS-graphic constant if the job CCSID is a single-byte CCSID with no associated DBCS CCSID.

VARCHAR Query for iSeries function

The VARCHAR function returns a varying-length character string representation of a string. VARCHAR

supports conversion from CHARACTER to VARCHAR and UCS-2 GRAPHIC to VARCHAR. Conversion of numeric type data is not supported. VARCHAR supports conversion from CHARACTER to VARCHAR and UCS-2 GRAPHIC to VARCHAR. Conversion of numeric type data is not supported.

VARCHAR ( expression

,

length

DEFAULT

)

, ccsid

The first argument must be a string expression; it must not be DBCS-graphic data.

The second argument, if specified as a length, is the length attribute of the resulting varying-length string. The second argument must be an integer constant between 1 and 32740 if the first argument is not nullable or between 1 and 32739 if the first argument is nullable. If the first argument is mixed data, the second argument cannot be less than 4. If the second argument is not specified or DEFAULT is specified, the result length is determined as follows, where n is the length attribute of the first argument.

vIf the first argument is SBCS or mixed data, the result length is n.

vIf the first argument is UCS2 graphic data and the result is SBCS data, the result length is n.

Chapter 5. Defining result fields in Query for iSeries 63