DB2 Version 9.7 for Linux, UNIX, and Windows

CAST specification


cast-specification

|--CAST--(--+---+-expression-------+----AS--| data-type |--+---------------------------------+-+-->
            |   +-NULL-------------+                       |       (1)                       | |   
            |   '-parameter-marker-'                       '-SCOPE------+-typed-table-name-+-' |   
            |                                                           '-typed-view-name--'   |   
            +-cursor-cast-specification--------------------------------------------------------+   
            '-row-cast-specification-----------------------------------------------------------'   

>--)------------------------------------------------------------|

cursor-cast-specification

|--parameter-marker--AS--+-CURSOR-----------+-------------------|
                         '-cursor-type-name-'   

row-cast-specification

|--+-row-expression---+--AS----row-type-name--------------------|
   +-NULL-------------+                          
   '-parameter-marker-'                          

data-type

|--+-| built-in-type |----+-------------------------------------|
   +-distinct-type-name---+   
   +-structured-type-name-+   
   '-REF--(type-name2)----'   

built-in-type

|--+-+-SMALLINT----+---------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                   |   
   | | '-INT-----' |                                                   |   
   | '-BIGINT------'                                                   |   
   |                  .-(5,0)-------------------.                      |   
   +-+-+-DECIMAL-+-+--+-------------------------+----------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                      |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                      |   
   |   '-NUM-----'               '-,integer-'                          |   
   |          .-(53)------.                                            |   
   +-+-FLOAT--+-----------+--+-----------------------------------------+   
   | |        '-(integer)-'  |                                         |   
   | +-REAL------------------+                                         |   
   | |         .-PRECISION-. |                                         |   
   | '-DOUBLE--+-----------+-'                                         |   
   |           .-(34)-.                                                |   
   +-DECFLOAT--+------+------------------------------------------------+   
   |           '-(16)-'                                                |   
   |                    .-(1)-------.                                  |   
   +-+-+-+-CHARACTER-+--+-----------+----------+--+----------------+-+-+   
   | | | '-CHAR------'  '-(integer)-'          |  +-FOR BIT DATA---+ | |   
   | | '-+-VARCHAR----------------+--(integer)-'  +-FOR SBCS DATA--+ | |   
   | |   '-+-CHARACTER-+--VARYING-'               '-FOR MIXED DATA-' | |   
   | |     '-CHAR------'                                             | |   
   | |                                  .-(1M)-------------.         | |   
   | '-+-CLOB------------------------+--+------------------+---------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-)-'           |   
   |     '-CHAR------'                             +-K-+               |   
   |                                               +-M-+               |   
   |                                               '-G-'               |   
   |            .-(1)-------.                                          |   
   +-+-GRAPHIC--+-----------+-------+----------------------------------+   
   | |          '-(integer)-'       |                                  |   
   | +-VARGRAPHIC--(integer)--------+                                  |   
   | |         .-(1M)-------------. |                                  |   
   | '-DBCLOB--+------------------+-'                                  |   
   |           '-(integer-+---+-)-'                                    |   
   |                      +-K-+                                        |   
   |                      +-M-+                                        |   
   |                      '-G-'                                        |   
   |                          .-(1M)-------------.                     |   
   +-+-BLOB----------------+--+------------------+---------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                     |   
   |                                     +-K-+                         |   
   |                                     +-M-+                         |   
   |                                     '-G-'                         |   
   +-+-DATE---------------------+--------------------------------------+   
   | +-TIME---------------------+                                      |   
   | |            .-(6)-------. |                                      |   
   | '-TIMESTAMP--+-----------+-'                                      |   
   |              '-(integer)-'                                        |   
   | .-SYSPROC.-.                                                      |   
   '-+----------+--DB2SECURITYLABEL------------------------------------'

Notes:

The SCOPE clause only applies to the REF data type.

The CAST specification returns the cast operand (the first operand) cast to the type specified by the data-type. If the cast is not supported, an error is returned (SQLSTATE 42846).

expression

If the cast operand is an expression (other than parameter marker or NULL), the result is the argument value converted to the specified target data-type.

When casting character strings (other than CLOBs) to a character string with a different length, a warning (SQLSTATE 01004) is returned if truncation of other than trailing blanks occurs. When casting graphic character strings (other than DBCLOBs) to a graphic character string with a different length, a warning (SQLSTATE 01004) is returned if truncation of other than trailing blanks occurs. For BLOB, CLOB and DBCLOB cast operands, the warning is issued if any characters are truncated.

When casting an array, the target data type must be a user-defined array data type (SQLSTATE 42821). The data type of the elements of the array must be the same as the data type of the elements of the target array data type (SQLSTATE 42846). The cardinality of the array must be less than or equal to the maximum cardinality of the target array data type (SQLSTATE 2202F).

NULL

If the cast operand is the keyword NULL, the result is a null value that has the specified data-type.

parameter-marker

A parameter marker is normally considered an expression, but is documented separately in this case because it has a special meaning. If the cast operand is a parameter-marker, the specified data-type is considered a promise that the replacement will be assignable to the specified data type (using store assignment for strings). Such a parameter marker is considered a typed parameter marker. Typed parameter markers will be treated like any other typed value for the purpose of function resolution, DESCRIBE of a select list or for column assignment.

cursor-cast-specification

A cast specification used to indicate that a parameter marker is expected to be a cursor type. It can be used wherever an expression is supported in contexts that allow cursor types.

parameter-marker: The cast operand is a parameter marker and is considered a promise that the replacement will be assignable to the specified cursor type.
CURSOR: Specifies the built-in data type CURSOR.
cursor-type-name: Specifies the name of a user-defined cursor type.

row-cast-specification

A cast specification where the input is a row value and the result is a user-defined row type. A row-cast-specification is only valid where a row-expression is allowed.

row-expression: The data type of row-expression must be a variable of row type that is anchored to the definition of a table or view. The data type of row-expression must not be a user-defined row type (SQLSTATE 42846).
NULL: Specifies that the cast operand is the null value. The result is a row with the null value for every field of the specified data type.
parameter-marker: The cast operand is a parameter marker and is considered a promise that the replacement will be assignable to the specified row-type-name.
row-type-name: Specifies the name of a user-defined row type. The row-expression must be castable to row-type-name (SQLSTATE 42846).

data-type

The name of an existing data type. If the type name is not qualified, the SQL path is used to perform data type resolution. A data type that has associated attributes, such as length or precision and scale, should include these attributes when specifying data-type. (CHAR defaults to a length of 1, DECIMAL defaults to a precision of 5 and a scale of 0, and DECFLOAT defaults to a precision of 34 if not specified.) The FOR SBCS DATA clause or the FOR MIXED DATA clause (only one is supported depending on whether or not the database supports the graphic data type) can be used to cast a FOR BIT DATA string to the database code page. Restrictions on the supported data types are based on the specified cast operand.

For a cast operand that is an expression, the supported target data types depend on the data type of the cast operand (source data type).
For a cast operand that is the keyword NULL, any existing data type can be used.
For a cast operand that is a parameter marker, the target data type can be any existing data type. If the data type is a user-defined distinct type, the application using the parameter marker will use the source data type of the user-defined distinct type. If the data type is a user-defined structured type, the application using the parameter marker will use the input parameter type of the TO SQL transform function for the user-defined structured type.

SCOPE

When the data type is a reference type, a scope may be defined that identifies the target table or target view of the reference.

typed-table-name: The name of a typed table. The table must already exist (SQLSTATE 42704). The cast must be to data-type REF(S), where S is the type of typed-table-name (SQLSTATE 428DM).
typed-view-name: The name of a typed view. The view must exist or have the same name as the view being created that includes the cast as part of the view definition (SQLSTATE 42704). The cast must be to data-type REF(S), where S is the type of typed-view-name (SQLSTATE 428DM).

When numeric data is cast to character data, the result data type is a fixed-length character string. When character data is cast to numeric data, the result data type depends on the type of number specified. For example, if cast to integer, it becomes a large integer.

Examples

An application is only interested in the integer portion of the SALARY (defined as decimal(9,2)) from the EMPLOYEE table. The following query, including the employee number and the integer value of SALARY, could be prepared.
```
   SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE
```
Assume the existence of a distinct type called T_AGE that is defined on SMALLINT and used to create column AGE in PERSONNEL table. Also assume the existence of a distinct type called R_YEAR that is defined on INTEGER and used to create column RETIRE_YEAR in PERSONNEL table. The following update statement could be prepared.
```
   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, although the application will use an integer for this parameter marker. This does not require the explicit CAST specification because it is an assignment.

The second parameter marker is a typed parameter marker that is cast as a distinct type T_AGE. This satisfies the requirement that the comparison must be performed with compatible data types. The application will use the source data type (which is SMALLINT) for processing this parameter marker.

Successful processing of this statement assumes that the SQL path includes the schema name of the schema (or schemas) where the two distinct types are defined.
An application supplies a value that is a series of bits, for example an audio stream, and it should not undergo code page conversion before being used in an SQL statement. The application could use the following CAST:
```
   CAST( ? AS VARCHAR(10000) FOR BIT DATA)
```

Assume that an array type and a table have been created as follows:

   CREATE TYPE PHONELIST AS DECIMAL(10, 0) ARRAY[5]

   CREATE TABLE EMP_PHONES
     (ID          INTEGER,
      PHONENUMBER DECIMAL(10,0) )

The following procedure returns an array with the phone numbers for the employee with ID 1775. If there are more than five phone numbers for this employee, an error is returned (SQLSTATE 2202F).

   CREATE PROCEDURE GET_PHONES(OUT EPHONES PHONELIST)
   BEGIN
     SELECT CAST(ARRAY_AGG(PHONENUMBER) AS PHONELIST)
     INTO EPHONES
     FROM EMP_PHONES
     WHERE ID = 1775;
   END