Considering Data Types

The value that the INSERT statement puts into a column does not need to be of the same data type as the column that receives it. These two data types, however, must be compatible. Two data types are compatible if the database server has some way to cast one data type to another. A cast is the mechanism by which the database server converts one data type to another.

The database server makes every effort to perform data conversion. If the data cannot be converted, the INSERT operation fails. Data conversion also fails if the target data type cannot hold the value that is specified. For example, you cannot insert the integer 123456 into a column defined as a SMALLINT data type because this data type cannot hold a number that large.

For a summary of the casting that the database server provides, see the HCL OneDB™ Guide to SQL: Reference. For information on how to create a user-defined cast, see the CREATE CAST statement in this document and HCL OneDB User-Defined Routines and Data Types Developer's Guide.

In a database that uses a nondefault locale, if the GL_DATETIME environment variable has a nondefault setting, the USE_DTENV environment variable must be set to 1 before the INSERT statement can correctly insert localized DATETIME values into a database table, or into a view, or into an EXTERNAL table object.