unpacked-row

An unpacked-row specifies a row that is the result of an invocation of the UNPACK built-in function.


unpacked-row

                                          .-,---------------------.      
                                          V                       |      
|--UNPACK-function-invocation--.*--AS--(----field-name--data-type-+--)--|

UNPACK-function-invocation

Specifies an invocation of the UNPACK built-in function. The number of specified field-names and field-types must be the same as the number of fields that are returned by the UNPACK function invocation.

field-name

Names the field that is returned from the UNPACK function. A name must not be qualified, and it does not have to be unique.

data-type

Specifies the built-in data type of the field. The specified data type, length, and CCSID must correspond to the data type, length, and CCSID of the data when the argument was initially encoded with the PACK function.

The following table provides the supported data type mappings from the packed string data:

Table 1. Data type mappings from packed string data
Data type of an encoded value in the packed string for UNPACK	Data type specified for UNPACK
SMALLINT	SMALLINT, INTEGER, BIGINT
INTEGER	INTEGER, BIGINT
BIGINT	BIGINT
decimal (p,s)¹	decimal(p', s') if s'< s, s-s' digits are truncated. An error occurs if there are more than p'-s' significant digits.
real² or double³	double
CHAR(n) or VARCHAR(n)	CHAR(m), VARCHAR(m) If m < n and any of the n-m characters is not a blank, an error occurs. Otherwise, the n-m blanks are truncated. If m > n and the specified data type is CHAR, m-n blanks are appended.
BINARY(n) or VARBINARY(n)	BINARY(m), VARBINARY(m) If m < n, an error occurs. If m > n and the UNPACK target is BINARY, m-n X'00' bytes are appended.
DATE	DATE
TIME	TIME
TIMESTAMP(p) WITHOUT TIME ZONE	TIMESTAMP(p') WITHOUT TIME ZONE. If p' > p, p'-p zeros are appended. If p' < p, p-p' digits are truncated.
TIMESTAMP(p) WITH TIME ZONE	TIMESTAMP(p') WITH TIME ZONE. If p' > p, p'-p zeros are appended. If p' < p, p-p' digits are truncated.
Note: The data types in lower case are defined as follows: decimal = DECIMAL(p,s) or NUMERIC(p,s) real = REAL or FLOAT(n) where n is the specification for a single precision floating point double = DOUBLE, DOUBLE PRECISION, FLOAT or FLOAT(n) where n is the specification for a double precision floating point The synonyms for the data types, in either long or short form, are considered the same as those that are listed.