>>-CAST--(--+-expression-------+--AS--data-type--)------------->< +-NULL-------------+ '-parameter-marker-' data-type: |--+-built-in-type-+--------------------------------------------| '-distinct-type-'
built-in-type: |--+-+---SMALLINT---+-----------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-NUMERIC-----' '-(--integer--+-----------+--)-' | | '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-----------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-CLOB-------------------+----+----------------------+--+----------------+-' | | +-CHAR LARGE OBJECT------+ '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CHARACTER LARGE OBJECT-' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-----------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+-------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +-----DATALINK--+---------------+--+--------------+--------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | '---ROWID--------------------------------------------------------------------------' ccsid-clause: .-NOT NORMALIZED-. |--CCSID--integer--+----------------+---------------------------| '-NORMALIZED-----'
The CAST specification returns the cast operand (the first operand) cast to the type specified by the data-type. If the data type of either operand is a distinct type, the privileges held by the authorization ID of the statement must include USAGE authority on the distinct type.
The supported casts are shown in Table 13, where the first column represents the data type of the cast operand (source data type) and the data types across the top represent the target data type of the CAST specification. If the cast is not supported, an error is returned.
When casting character or graphic strings to a character or graphic string with a different length, a warning is returned if truncation of other than trailing blanks occurs.
Restrictions on the supported data types are based on the specified cast operand.
If the CCSID attribute is not specified, then:
If the CCSID attribute is specified, the data will converted to that CCSID. If NORMALIZED is specified, the data will be normalized.
For information on which casts between data types are supported and the rules for casting to a data type see Casting between data types.
SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE
UPDATE PERSONNEL SET RETIRE_YEAR = ? WHERE AGE = CAST( ? AS T_AGE )
The first parameter is an untyped parameter marker that would have a data type of R_YEAR. An explicit CAST specification is not required in this case because the parameter marker value is assigned to the distinct type.
The second parameter marker is a typed parameter marker that is cast to distinct type T_AGE. An explicit CAST specification is required in this case because the parameter marker value is compared to the distinct type.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.