Casting between distinct types

This topic describes casting between distinct types.

About this task

Suppose you want to define a UDF that converts another currency into U.S. dollars. For the purposes of this example, you can obtain the current exchange rate from a table such as the following query:

CREATE TABLE
  exchange_rates(source CHAR(3), target CHAR(3), rate DECIMAL(9,3))

The following function can be used to directly access the values in the exchange_rates table:

CREATE FUNCTION exchange_rate(src VARCHAR(3), trg VARCHAR(3))
  RETURNS DECIMAL(9,3)
  RETURN SELECT rate FROM exchange_rates
    WHERE source = src AND target = trg

The currency exchange rates in the preceding function are based on the DECIMAL type, not distinct types. To represent some different currencies, use the following distinct type definitions:

CREATE DISTINCT TYPE CANADIAN_DOLLAR AS DECIMAL (9,3) WITH COMPARISONS
CREATE DISTINCT TYPE EURO AS DECIMAL(9,3) WITH COMPARISONS
CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,3) WITH COMPARISONS

To create a UDF that converts CANADIAN_DOLLAR or EURO to US_DOLLAR you must cast the values involved. Note that the exchange_rate function returns an exchange rate as a DECIMAL. For example, a function that converts values of CANADIAN_DOLLAR to US_DOLLAR performs the following steps:

Procedure

cast the CANADIAN_DOLLAR value to DECIMAL
get the exchange rate for converting the Canadian dollar to the U.S. dollar from the exchange_rate function, which returns the exchange rate as a DECIMAL value
multiply the Canadian dollar DECIMAL value to the DECIMAL exchange rate
cast this DECIMAL value to US_DOLLAR
return the US_DOLLAR value

What to do next

The following are instances of the US_DOLLAR function (for both the Canadian dollar and the euro), which follow the preceding steps.

CREATE FUNCTION US_DOLLAR(amount CANADIAN_DOLLAR)
  RETURNS US_DOLLAR
  RETURN US_DOLLAR(DECIMAL(amount) * exchange_rate('CAN', 'USD'))

CREATE FUNCTION US_DOLLAR(amount EURO)
  RETURNS US_DOLLAR
  RETURN US_DOLLAR(DECIMAL(amount) * exchange_rate('EUR', 'USD'))