An example of casting between distinct data types

Suppose you want 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 must create cast 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.

The following example shows how you might define three distinct types on the same source type, DOUBLE PRECISION:

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. The following example creates the manufact_price table, which contains a column for the dollar, yen, and sterling distinct types:

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 correct distinct type for dollar, yen, and sterling values, as follows:

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

Because a distinct type does not inherit any of the built-in casts available to its source type, each of the preceding INSERT statements requires two casts. For each INSERT statement, the inner cast converts from DECIMAL to DOUBLE PRECISION and the outer cast converts from DOUBLE PRECISION to the correct distinct type (dollar, yen, or sterling).

Before you can compare the dollar, yen, and sterling data types, you must create cast functions and register them as casts. The following example creates SPL functions that you can use to compare dollar, yen, and sterling values. Each function multiplies the input value by a value that reflects the exchange rate.

CREATE FUNCTION dollar_to_yen(d dollar)
   RETURN (d::DOUBLE PRECISION * 106)::CHAR(20)::yen;
END FUNCTION;

CREATE FUNCTION sterling_to_dollar(s sterling)
   RETURNS dollar
   RETURN (s::DOUBLE PRECISION * 1.59)::CHAR(20)::dollar;
END FUNCTION;

After you write the cast functions, you must use the CREATE CAST statement to register the functions as casts. The following statements register the dollar_to_yen() and sterling_to_dollar() functions as explicit casts:

CREATE CAST(dollar AS yen WITH dollar_to_yen);
CREATE CAST(sterling AS dollar WITH sterling_to_dollar);

After 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 cast function and register it as a cast, see the CREATE FUNCTION and CREATE CAST statements in the HCL OneDB™ 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;

The following query uses the cast operator to perform the same conversion shown in the preceding query:

SELECT * FROM manufact_price
   WHERE us_price::yen < japan_price;

You can also use an explicit cast to convert the values that a query returns. The following query uses a cast to return yen equivalents of dollar values. The WHERE clause of the query also uses an explicit cast to compare dollar and yen values.

SELECT us_price::yen, japan_price FROM manufact_price
   WHERE us_price::yen < japan_price;