Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 13: Casting Data Types

Home Contents Index Master Index New Book

An Example of Casts with Conversion Functions

If your database contains opaque data types, distinct data types, or named row types, you might want to create user-defined casts that allow you to convert between the different data types. When you wish to perform conversions between two data types that have the same storage structure, you can use the CREATE CAST statement without a conversion function. However, in some cases you must create a conversion function that you then register as a cast. You need to create a conversion function under the following conditions:

The conversion is between two data types that have different storage structures
The conversion involves the manipulation of values to ensure that data conversions are meaningful

The following sections show how to create and use casts that you create with a conversion function.

Creating a Conversion Function Cast

Suppose you wish to define distinct types to represent dollar, yen, and sterling currencies. Any comparison between two currencies must take the exchange rate into account. Thus, you need to create conversion functions that not only handle the cast from one data type to the other data type but also calculate the exchange rate for the values that you want to compare.

Figure 13-10 shows how you might define three distinct types on the same source type, DOUBLE PRECISION.

Figure 13-10

CREATE DISTINCT TYPE dollar AS DOUBLE PRECISION;


CREATE DISTINCT TYPE yen AS DOUBLE PRECISION;


CREATE DISTINCT TYPE sterling AS DOUBLE PRECISION;

After you define the distinct types, you can create a table that provides the prices that manufacturers charge for comparable products. Figure 13-11 creates the manufact_price table, which contains a column for the dollar, yen, and sterling distinct types.

Figure 13-11

CREATE TABLE manufact_price 
( 
product_desc VARCHAR(20),
us_price dollar, 
japan_price yen, 
uk_price sterling
);

When you insert values into the manufact_price table, you can cast to the appropriate distinct type for dollar, yen, and sterling values, as follows:

INSERT INTO manufact_price
VALUES ('baseball', 5.00::dollar, 510.00::yen, 
3.50::sterling)

Before you can compare the dollar, yen, and sterling data types, you must create conversion functions and register them as casts. Figure 13-12 shows how to create an SPL function, dollar_to_yen(), that you can use to compare dollar and yen values. To account for the exchange rate, the function multiplies dollar values by 106 to derive equivalent yen values.

Figure 13-12

CREATE FUNCTION dollar_to_yen(d dollar) 

RETURNS yen 
RETURN CAST((d::DOUBLE PRECISION * 106) AS yen);
END FUNCTION;

Figure 13-13 creates an SPL function to compare sterling and dollar values. To account for the exchange rate, the function multiplies sterling values by 1.59 to derive equivalent dollar values.

Figure 13-13

CREATE FUNCTION sterling_to_dollar(s sterling) 

RETURNS dollar 
RETURN CAST((s::DOUBLE PRECISION * 1.59) AS dollar);
END FUNCTION;

Once you write the conversion functions, you must use the CREATE CAST statement to register the functions as casts. Figure 13-14 shows how to register the dollar_to_yen() and sterling_to_dollar() functions as explicit casts.

Figure 13-14

CREATE CAST(dollar AS yen WITH dollar_to_yen);


CREATE CAST(sterling AS dollar WITH sterling_to_dollar);

Once you register the function as a cast, use it for operations that require conversions between the data types. For the syntax that you use to create a conversion function and register it as a cast, see the CREATE FUNCTION and CREATE CAST statements in the Informix Guide to SQL: Syntax.

In the following query, the WHERE clause includes an explicit cast that invokes the dollar_to_yen() function to compare dollar and yen values:

SELECT * 
FROM manufact_price
WHERE CAST(us_price AS yen) < japan_price

You can also use a cast to convert values that a query returns. The following query includes a cast so that dollar values are returned as their yen equivalents. The WHERE clause of the query also uses an explicit cast to compare dollar and yen values.

SELECT CAST(us_price AS yen), japan_price
FROM manufact_price
WHERE CAST(us_price AS yen) < japan_price

Performing MultiLevel Casts with Explicit Casts

Up to this point, all the cast examples have been single-level casts. A single-level cast is simply an operation that requires one and only one cast to convert a value of one data type to the desired data type. A single-level cast can be implicit or explicit.

A multilevel cast refers to an operation that requires two or more levels of casting in an expression to convert a value of one data type to another data type. A multilevel cast can include implicit and/or explicit casts. In some cases, the database server might use several system-defined casts to implicitly cast a value of one data type to another data type. In other cases, you might need to use multiple explicit casts to convert between two data types, as the following query shows. Because no cast exists for direct comparisons between yen and sterling values, the query requires two explicit casts. The first (inner) cast converts sterling values to dollar values; the second (outer) cast converts dollar values to yen values.

SELECT * 
FROM manufact_price 
WHERE japan_price < uk_price::dollar::yen

The preceding query requires two levels of casting to get from sterling to yen because a sterling to yen cast does not exist in the database.

You might add another casting function to handle yen to sterling conversions directly. Figure 13-15 shows how to create the function yen_to_sterling() and register it as a cast. To account for the exchange rate, the function multiplies yen values by.01 to derive equivalent sterling values.

Figure 13-15

CREATE FUNCTION yen_to_sterling(y yen) 
RETURNS sterling 
RETURN CAST((y::DOUBLE PRECISION * .01) AS sterling); 
END FUNCTION;



CREATE CAST (yen AS sterling WITH yen_to_sterling);

With the addition of the cast in Figure 13-15, you can use a single-level cast to compare yen and sterling values, as the following query shows. In the SELECT statement, the explicit cast is used to return yen values as their sterling equivalents. In the WHERE clause, the cast is used to compare yen and sterling values.

SELECT japan_price, uk_price
FROM manufact_price
WHERE CAST(japan_price AS sterling) < uk_price;:

Informix Guide to SQL: TutorialChapter 13: Casting Data Types Home Contents Index Master Index New Book

An Example of Casts with Conversion Functions

Creating a Conversion Function Cast

Performing MultiLevel Casts with Explicit Casts

Informix Guide to SQL: Tutorial
Chapter 13: Casting Data Types

Home Contents Index Master Index New Book