Informix Guide to SQL: Tutorial
Chapter 13: Casting Data Types
Home
Contents
Index
Master Index
New Book
Casting Collection Data Types
In some cases, you can use an explicit cast to convert from one collection type to another collection type. To compare or substitute between values of any two collection types, one of the following conditions must be true:
The element types of the two collection types are the same.
Two element types are equivalent when all component types are the same. For example, if the element type of one collection is a row type, the other collection type is also a row type with the same number of fields and the same field data types.
Casts exist in the database to perform conversions between any and all components of the element types that are not of the same data type.
If the corresponding element types are not of the same data type, Universal Server can use either system-defined casts or user-defined casts to handle data conversions on the element types.
When the database server inserts, updates, or compares values of a collection data type, type checking occurs at the level of the element data type. Consequently, in a cast between two collection types, the data conversion occurs at the level of the element type because the actual data stored in a collection is of a particular element type.
Suppose you create the types and tables that
Figure 13-7
shows. These types and tables are used in the collection casting examples that follow.
Figure 13-7
CREATE DISTINCT TYPE my_int AS INT;
CREATE TABLE set_tab1 (col1 SET(my_int NOT NULL));
CREATE TABLE set_tab2(col2 SET(INT NOT NULL));
CREATE TABLE set_tab3 (col3 SET(FLOAT NOT NULL));
CREATE TABLE list_tab (col4 LIST(INT NOT NULL));
CREATE TABLE m_set_tab(col5 MULTISET(INT NOT NULL));
Converting Between Collection Types with the Same Element Type
When the element type of two collections is the same but the collection types differ, you can insert or update elements from one collection with elements from the other collection without an explicit cast. The following
INSERT
statement retrieves elements from the
list_tab
table and inserts the elements into the
m_set_tab
table. Although one collection is a
MULTISET
and the other collection is a
LIST
, no explicit cast is necessary because the element types of the two collection types are the same (both are of the
INT
element type).
INSERT INTO m_set_tab SELECT col4 FROM list_tab
Because each collection data type (
SET
,
MULTISET
, and
LIST
) has different characteristics, elements retrieved from one collection type and inserted into another collection type are represented differently. For example, elements stored in a
LIST
collection have a specific order associated with them. This order is lost when these same elements are inserted into a
MULTISET
collection.
Converting Between Collections with Different Element Types
How you handle conversions between two collections depends on the element type of each collection and the type of cast that the database server uses to convert one element type to another when the element types are different:
If a system-defined cast or implicit user-defined cast exists to handle the conversion between two element types, you do not need to explicitly cast between the collection types.
If an explicit cast exists to handle the conversion between element types, you must explicitly cast between the collection types.
When the Conversion Between Element Types Requires an Implicit Cast
When an implicit cast exists in the database to convert between different element types of two collections, you do not need to use an explicit cast to insert or update elements from one collection type into the other collection type. The following
INSERT
statement retrieves elements from the
set_tab2
table and inserts the elements into the
set_tab3
table. Although the collection column from
set_tab2
has an
INT
element type and the collection column from
set_tab3
has a
FLOAT
element type, a system-defined cast implicitly handles the conversion between
INT
and
FLOAT
values. An explicit cast is unnecessary in this case.
INSERT INTO set_tab3 SELECT col2
FROM set_tab2
When the Conversion Between Element Types Requires an Explicit Cast
When a conversion between different element types of two collections is performed with an explicit cast, you must explicitly cast one collection to the other collection type. In the following example, the conversion between the element types (
INT
and
my_int
) requires an explicit cast. (A cast between a distinct type and its source type is always explicit).
The following
INSERT
statement retrieves elements from the
set_tab2
table and inserts the elements into the
set_tab1
table. The collection column from
set_tab2
has an
INT
element type and the collection column from
set_tab1
has a
my_int
element type. Because the conversion between the element types (
INT
and
my_int
) requires an explicit cast, you must explicitly cast the collection type.
INSERT INTO set_tab1 SELECT col2::SET(my_int NOT NULL)
FROM set_tab2
To perform an explicit cast on a collection type, you must include the constructor (
SET
,
MULTISET
, or
LIST
), the element type, and the
NOT NULL
keyword.
The following
INSERT
statement retrieves elements from the
m_set_tab
table and inserts the elements into the
set_tab1
. The explicit cast is necessary because a conversion between the
INT
and
my_int
element types
requires an explicit cast. This example differs from the previous example in that here a MULTISET collection is explicitly cast as a SET collection.
INSERT INTO set_tab1 SELECT col5::SET(my_int NOT NULL)
FROM m_set_tab
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
