Informix Guide to SQL: Reference

Informix Guide to SQL: Reference
Chapter 2: Data Types

Home Contents Index Master Index New Book

Data Type Casting

There may be times when the data type that was assigned to a column with the CREATE TABLE statement is inappropriate. The database server allows you to change the data type of the column or to cast its values to a different data type with either of the following methods:

You can use the ALTER TABLE statement to modify the data type of a column.

You can use the CAST AS keywords or the double colon (::) cast operator to cast a value to a different data type.

Casting does not permanently alter the data type of a value; it expresses the value in a more convenient form. Casting user-defined data types into built-in types allows client programs to manipulate data types without knowledge of their internal structure.

Both data type conversion and casting depend on casts defined in the syscasts system catalog table.

A cast is either system-defined, or else explicit or implicit.

Using System-Defined Casts

System-defined casts are owned by user informix. They govern conversions from one built-in data type to another. System-defined casts allow the database server to convert:

A character type to any other character type
A character type to or from any other built-in type
A numeric type to any other numeric type
A time data type to or from a datetime type

The database server automatically invokes the appropriate system-defined casts when required. An infinite number of system-defined casts that may be invoked to evaluate and compare expressions or to change a column from one built-in data type to another.

When you convert a column from one built-in data type to another, the database server applies the appropriate system-defined casts to each value already in the column. If the new data type cannot store any of the resulting values, the ALTER TABLE statement fails.

For example, if you try to convert a column from the INTEGER data type to the SMALLINT data type and the following values exist in the INTEGER column, the database server does not change the data type because SMALLINT columns cannot accommodate numbers greater than 32,767:

100	400	700	50000	700

The same situation might occur if you attempt to transfer data from FLOAT or SMALLFLOAT columns to INTEGER, SMALLINT, or DECIMAL columns.

The following sections describe database server behavior during certain types of casts and conversions.

Converting Number to Number

When you convert data from one numeric type to another, you occasionally find rounding errors. Figure 2-7 indicates which numeric data-type conversions are acceptable and what kinds of errors you can encounter when you convert between certain numeric data types.

Figure 2-7
Numeric Data Type Conversion Chart

TO
FROM

SMALLINT INTEGER INT8 SMALLFLOAT FLOAT DECIMAL
SMALLINT OK

OK

OK

OK

OK

OK

INTEGER E

OK

OK

E

OK

P

INT8 E

E

OK

D

E

P

SMALLFLOAT E

E

E

OK

OK

P

FLOAT E

E

E

D

OK

P

DECIMAL E

E

E

D

D

P

Legend:
OK = No error
P = An error can occur depending on the precision of the decimal
E = An error can occur depending on data
D = No error, but less significant digits might be lost

For example, if you convert a FLOAT value to DECIMAL(4,2), your database server rounds off the floating-point numbers before storing them as decimal numbers. This conversion can result in an error depending on the precision assigned to the DECIMAL column.

Converting Between Number and CHAR

You can convert a CHAR (or NCHAR) column to a numeric column. However, if the CHAR or NCHAR column contains any characters that are not valid in a numeric column (for example, the letter l instead of the number 1), your database server returns an error.

You can also convert a numeric column to a character column. However, if the character column is not large enough to receive the number, the database server generates an error.

If the database server generates an error, it cannot complete the ALTER TABLE statement or cast and leaves the column values as characters. You receive an error message and the statement is rolled back (whether you are in a transaction or not).

Converting Between an Integer and DATE or DATETIME

You can convert an integer column (SMALLINT, INTEGER, or INT8) to a DATE or DATETIME value. The database server interprets the integer as a value in the internal format of the DATE or DATETIME column. You can also convert a DATE or DATETIME column to an integer column. The database server stores the internal format of the DATE or DATETIME column as an integer.

For a DATE column, the internal format is a Julian date. For a DATETIME column, the internal format stores the date and time in a condensed integer format.

Converting Between DATE and DATETIME

You can convert DATE columns to DATETIME columns. However, if the DATETIME column contains more fields than the DATE column, the database server either ignores the fields or fills them with zeros. The illustrations in the following list show how these two data types are converted (assuming that the default date format is mm/dd/yyyy):

If you convert DATE to DATETIME YEAR TO DAY, the database server converts the existing DATE values to DATETIME values. For example, the value 08/15/1994 becomes 1994-08-15.
If you convert DATETIME YEAR TO DAY to the DATE format, the value 1994-08-15 becomes 08/15/1994.
If you convert DATE to DATETIME YEAR TO SECOND, the database server converts existing DATE values to DATETIME values and fills in the additional DATETIME fields with zeros. For example, 08/15/1994 becomes 1994-08-15 00:00:00.
If you convert DATETIME YEAR TO SECOND to DATE, the database server converts existing DATETIME to DATE values but drops fields more precise than DAY. For example, 1994-08-15 12:15:37 becomes 08/15/1994.

Using Implicit Casts

Implicit casts are owned by the users who create them. They govern casts and conversions between user-defined data types and other data types.

Developers of user-defined data types must create certain implicit casts and the functions that are used to implement them. The casts allow user-defined types to be expressed in a form that clients can manipulate. They also allow you to convert a user-defined data type to a built-in type or vice versa.

The database server automatically invokes a single implicit cast when needed to evaluate and compare expressions or pass arguments. Operations that require more than one implicit cast fail.

Users can explicitly invoke an implicit cast using the CAST AS keywords or the double colon (::) cast operator.

For information on how to define implicit casts, see the CREATE CAST statement in Informix Guide to SQL: Syntax.

Using Explicit Casts

Explicit casts are owned by the users who create them. They govern casts between user-defined data types and other data types.

Developers of user-defined data types must create certain explicit casts and the functions that are used to implement them. The casts allow user-defined types to be expressed in a form that clients can manipulate. They do not allow you to convert a user-defined data type to a built-in type or vice versa.

Explicit casts, unlike implicit casts or system-defined casts, are never invoked automatically by the database server. Users must invoke them explicitly with the CAST AS keywords or the double colon (::) cast operator.

For information on how to define explicit casts, see the CREATE CAST statement in the Informix Guide to SQL: Syntax.

Determining Which Cast to Apply

The database server uses the following rules to determine which cast to apply in a particular situation:

The database server applies only one implicit cast per operand. If two or more casts are needed to convert the operand to the desired type, the user must explicitly invoke the additional casts.
To compare two built-in types, the database server automatically invokes the appropriate system-defined casts.
To compare a distinct type to its source type, the user must explicitly cast one type to the other.
To compare a distinct type to a type other than its source, the database server looks for:
- An implicit cast between the two types or
- An implicit cast between the source type and the desired type

If neither cast is registered, the user must invoke an explicit cast between the distinct type and the desired type. If this cast is not registered, the database server automatically invokes a cast from the source type to the desired type.

If none of these casts is defined, the comparison fails.

To compare an opaque type to a built-in type, the user must explicitly cast the opaque type to a form that the database server understands (LVARCHAR, SENDRECV, IMPEX, or IMPEXBIN). The database server then invokes system-defined casts to convert the results to the desired built-in type.
To compare two opaque types, the user must explicitly cast one opaque type to a form that the database server understands (LVARCHAR, SENDRECV, IMPEX, or IMPEXBIN), then explicitly cast this type to the second opaque type.

Casts for Distinct Types

You define a distinct type based on a built-in type or an existing opaque type or row type. Although data of the distinct type has the same length and alignment and is passed in the same way as data of the source type, the two cannot be compared directly. To compare a distinct type and its source type, you must explicitly cast one type to the other.

When you create a new distinct type, the database server automatically registers two explicit casts that allow you to do this:

A cast from the distinct type to its source type
A cast from the source type to the distinct type

You can create an implicit cast between a distinct type and its source type. However, to create an implicit cast, you must first drop the default explicit cast between the distinct type and its source type.

All casts that have been registered for the source type can also be used without modification on the distinct type. You can also define new casts and support functions that apply only to the distinct type.

For examples that show how to create a casting function for a distinct type and register the function as cast, see Chapter 13, "Casting Data Types," of the Informix Guide to SQL: Tutorial.

What Extended Data Types Can Be Cast?

The following table shows the data type combinations that you can cast. The table shows only whether or not a cast between a source type and a target type are possible. In some cases, you must first create a user-defined cast before you can perform a conversion between two data types. In other cases, the database server automatically provides a cast that is implicitly invoked or that you must explicitly invoke.


Target Type --->	Opaque Type	Distinct Type	Named Row Type	Unnamed Row Type	Collection Type	Built-in Type
Opaque Type	explicit or implicit	explicit	explicit3	`NA`	`NA`	explicit or implicit3
Distinct Type	explicit	explicit	explicit	`NA`	`NA`	explicit or implicit
Named Row Type	explicit3	explicit	explicit1	explicit1	`NA`	`NA`
Unnamed Row Type	`NA`	`NA`	explicit1	implicit1	`NA`	`NA`
Collection Type	`NA`	`NA`	`NA`	`NA`	explicit2	`NA`
Built-in Type	explicit or implicit3	explicit or implicit	`NA`	`NA`	`NA`	system defined (implicit)
1 Applies when two row types are structurally equivalent or casts exist to handle data conversions where corresponding field types are not the same. 2 Applies when a cast exists to convert between the element types of the respective collection types. 3 Applies when a user-defined cast exists to convert between the two data types. `NA` = Not Allowed

Informix Guide to SQL: Reference, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.