CREATE CAST statement

Use the CREATE CAST statement to register a cast that converts data from one data type to another.

This statement is an extension to the ANSI/ISO standard for SQL.


2  ( source_type AS target_type?  WITH function )
Element Description Restrictions Syntax
function UDR that you register to implement the cast See WITH Clause. Identifier
source_type Data type to be converted Must exist in the database at the time the cast is registered. See also Source and Target Data Types. Data Type
target_type Data type that results from the conversion The same restrictions that apply for the source_type (as listed above) also apply for the target_type Data Type


A cast is a mechanism that the database server uses to convert one data type to another. The database server uses casts to perform the following tasks:
  • To compare two values in the WHERE clause of a SELECT, UPDATE, or DELETE statement
  • To pass values as arguments to user-defined routines
  • To return values from user-defined routines

To create a cast, you must have the necessary privileges on both the source data type and the target data type. All users have access privileges to use the built-in data types. To create a cast to or from an OPAQUE, DISTINCT, or named ROW data type, however, requires the Usage privilege on that data type.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a cast between the two specified data types is already registered in the current database.

The CREATE CAST statement registers a cast in the syscasts system catalog table. For more information on syscasts, see the chapter on system catalog tables in the HCL OneDB™ Guide to SQL: Reference.