Informix Guide to SQL: Tutorial
Chapter 13: Casting Data Types
Home
Contents
Index
Master Index
New Book
Casting Row Types
You can compare or substitute between values of any two row types (named or unnamed) only if both row types have the same number of fields, and one of the following conditions is also true:
All corresponding fields of the two row types have the same data type.
Two row types are
structurally equivalent
when they have the same number of fields and the data types of corresponding fields are the same.
System-defined or user-defined casts exist to perform the necessary conversions for corresponding field values that are not of the same data type.
When the corresponding fields 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 fields.
If a system-defined cast exists to handle data conversions on the individual fields, you only need to explicitly cast the value of one row type to the other row type (unless the row types are both unnamed row types, in which case an explicit cast is not necessary).
If a system-defined cast does not exist to handle field conversions, you can create a user-defined cast. The cast can be either implicit or explicit.
In general, when a row type is cast to another row type, some fields might be cast explicitly while other fields are cast implicitly. When the conversion between corresponding fields requires an explicit cast, the value of the field that is cast must match the value of the corresponding field exactly because the database server applies no additional implicit casts on a value that has been explicitly cast.
Casting Between Named Row Types
A named row type is strongly typed, which means that two named row types are recognized by the database server as two separate types even if the row types are structurally equivalent.
Suppose you create two named row types and a table, as
Figure 13-1
shows. Although the named row types are structurally equivalent,
writer_t
and
editor_t
are unique data types.
Figure 13-1
CREATE ROW TYPE writer_t (name VARCHAR(30), depart CHAR(3));
CREATE ROW TYPE editor_t (name VARCHAR(30), depart CHAR(3));
CREATE TABLE projects
(
book_title VARCHAR(20),
writer writer_t,
editor editor_t
);
To compare a named row type with another named row type, you must explicitly cast one row type value to the other row type.
In the following example, values of type
writer_t
are explicitly cast as
editor_t
.
The explicit cast in the
WHERE
clause enables comparisons between values of type
writer_t
and
editor_t
. The query returns the titles of any books for which the writer is also the editor.
SELECT book_title
FROM projects
WHERE CAST(writer AS editor_t) = editor
If you prefer, you can use the :: cast operator to perform the same cast, as the following example shows:
SELECT book_title
FROM projects
WHERE writer::editor_t = editor
Casting Between Named and Unnamed Row Types
You must use an explicit cast for comparisons between a named row type and an unnamed row type. Suppose that you create a named row type and two tables, as
Figure 13-2
shows.
Figure 13-2
CREATE ROW TYPE info_t (x BOOLEAN, y BOOLEAN)
CREATE TABLE customer (cust_info info_t)
CREATE TABLE retailer (ret_info ROW (a CHAR(1), b CHAR(1)))
Universal Server provides a system-defined cast that handles conversions between the
BOOLEAN
and
CHAR
fields of the respective row types, but you must explicitly cast the value of the unnamed row type to a named row type. In the following query, the
ret_info
column (an unnamed row type) is explicitly cast to
info_t
(a named row type). The explicit cast enables you to make comparisons between the
cust_info
and
ret_info
columns.
SELECT cust_info
FROM customer, retailer
WHERE cust_info = ret_info::info_t
In general, to perform a conversion between a named row type and an unnamed row type, you must explicitly cast one row type to the other row type. You can perform an explicit cast in either direction: you can cast the named row type to an unnamed row type or cast the unnamed row type to a named row type. The following statement returns the same results as the previous example. However, the named row type in this example is explicitly cast to the unnamed row type:
SELECT cust_info
FROM customer, retailer
WHERE cust_info::ROW(a CHAR(1), b CHAR(1)) = ret_info
Before you can explicitly cast between two row types whose fields contain different data types, a cast (either system-defined or user-defined) must exist to perform conversions between the corresponding field data types. For example, to explicitly cast between values of the
info_t
type and an unnamed row type that is defined as
ROW(a INT, b INT)
, you must first create a user-defined cast that performs conversions between
INT
and
BOOLEAN
values. If such a cast has been registered in your database, you can explicitly cast values of the unnamed row type
ROW(a INT, b INT)
to the
info_t
type, to compare values of the two row types.
Casting Between Unnamed Row Types
You can compare two unnamed row types that are structurally equivalent without using an explicit cast.You can also compare an unnamed row type with another unnamed row type if both row types have the same number of fields, and casts exist that can convert values of corresponding fields that are not of the same data type. In other words, the cast from one unnamed row type to another is implicit if all the casts to handle field conversions are system-defined or implicit casts. Otherwise, you must explicitly cast an unnamed row type to compare it with another row type.
Suppose you create the table that
Figure 13-3
shows.
Figure 13-3
CREATE TABLE prices
(
col1 ROW(a SMALLINT, b FLOAT)
col2 ROW(x INT, y REAL)
);
The following query compares values of
col1
and
col2
of the
prices
table and returns rows where
col1
is equal to
col2
:
SELECT *
FROM prices
WHERE col1 = col2
The values of the two unnamed row types can be compared (without an explicit cast) when system-defined casts exist to perform conversions between the corresponding fields of the row types. In the preceding example, the database server automatically makes the necessary conversions between the corresponding fields of
col1
and
col2
, using system-defined casts that convert values of
SMALLINT
to
INT
and
REAL
to
FLOAT
.
If corresponding fields of two row types cannot implicitly cast to one another, you can explicitly cast between the types providing that a cast exists for data conversion between the two types. For example, suppose your database contains the distinct
types, table, and user-defined cast as
Figure 13-4
shows.
Figure 13-4
CREATE DISTINCT TYPE dollar AS DOUBLE PRECISION
CREATE DISTINCT TYPE yen AS DOUBLE PRECISION
CREATE TABLE imports(price ROW(x VARCHAR(20), y yen))
CREATE CAST (yen AS dollar)
Because a user-defined cast has been created to convert
yen
values to
dollar
values, you might explicitly cast the
price
column from the
imports
table as an unnamed row type in which values of type
yen
are converted to type
dollar
, as
Figure 13-5
shows.
Figure 13-5
INSERT INTO imports VALUES(ROW('chair', 5.76::yen))
SELECT price::ROW(x VARCHAR(20), y dollar) FROM imports
Row-Type Conversions that Require Explicit Casts on Fields
When you explicitly cast between two row types, the database server automatically invokes any explicit casts that are required to convert individual fields to the appropriate data type. In other words, you do not have to explicitly cast both the field and row type values.
Suppose you create the types and tables that
Figure 13-6
shows.
Figure 13-6
CREATE DISTINCT TYPE d_float AS FLOAT
CREATE ROW TYPE row_t (a INT, b d_float)
CREATE TABLE tab1 (col1 ROW (a INT, b d_float))
CREATE TABLE tab2(col2 ROW (a INT, b FLOAT))
CREATE TABLE tab3 (col3 row_t)
Explicit Casts on Fields of an Unnamed Row Type
When a conversion between two row types involves an explicit cast to convert between particular field values, you can explicitly cast the row type value but do not need to explicitly cast the individual field. For example, to substitute a value from
col1
of
tab1
into
col2
of
tab2
, you can explicitly cast the row value, as follows:
INSERT INTO tab2
SELECT col1::ROW(a INT, b FLOAT)
FROM tab1
In this example, the cast that is used to convert the
b
field is explicit because the conversion from
d_float
to
FLOAT
requires an explicit cast (to convert a distinct type to its source type requires an explicit cast).
In general, to cast between two unnamed row types where one or more of the fields uses an explicit cast, you must explicitly cast at the level of the row type, not at the level of the field.
Explicit Casts on Fields of a Named Row Type
When you explicitly cast a value as a named row type, the database server automatically invokes any implicit or explicit casts that are used to convert field values to the appropriate data type. In the following statement, the explicit cast of
col1
to type
row_t
also invokes the explicit cast that converts the FLOAT field value to a
d_float
value:
INSERT INTO tab3 SELECT col2::row_t
FROM tab2
The following
INSERT
statement includes an explicit cast to the
row_t
type. The explicit cast to the row type also invokes any explicit casts that are defined to handle conversions of individual field values.
INSERT INTO tab3
VALUES (ROW(5,6.55)::row_t)
The following statement is also valid and returns the same results as the preceding statement. However, this statement shows all the explicit casts that are performed to insert a
row_t
value into the
tab3
table.
INSERT INTO tab3
VALUES (ROW(5, 6.55::d_float)::row_t)
Casting Fields of a Row Type
If an operation on a field of a row type requires an explicit cast, you can explicitly cast the individual field value without consideration of the row type with which the field is associated. The following statement uses an explicit cast on the field value to handle the conversion:
SELECT col1 from tab1, tab2
WHERE col1.b = col2.b::FLOAT
If an operation on a field of a row type requires an implicit cast, you can simply specify the appropriate field value and the database server handles the conversion automatically. In the following statement, which compares field values of different data types, the cast is handled automatically because a system-defined cast converts between
INT
and
FLOAT
values:
SELECT col1 from tab1, tab2
WHERE col1.a = col2.b
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
