The following are examples of manipulating distinct types:
Suppose you want to know which products sold more than US $100 000.00 in the US in the month of July, 1999 (7/99).
SELECT PRODUCT_ITEM
FROM US_SALES
WHERE TOTAL > US_DOLLAR (100000)
AND month = 7
AND year = 1999
Because you cannot compare US dollars with instances of the source type of US dollars (that is, DECIMAL) directly, you have used the cast function provided by DB2 to cast from DECIMAL to US dollars. You can also use the other cast function provided by DB2 (that is, the one to cast from US dollars to DECIMAL) and cast the column total to DECIMAL. Either way you decide to cast, from or to the distinct type, you can use the cast specification notation to perform the casting, or the functional notation. That is, you could have written the above query as:
SELECT PRODUCT_ITEM
FROM US_SALES
WHERE TOTAL > CAST (100000 AS us_dollar)
AND MONTH = 7
AND YEAR = 1999
Suppose you want to define a UDF that converts Canadian dollars to U.S. dollars. Suppose you can obtain the current exchange rate from a file managed outside of DB2. You would then define a UDF that obtains a value in Canadian dollars, accesses the exchange rate file, and returns the corresponding amount in U.S. dollars.
At first glance, such a UDF may appear easy to write. However, C does not support DECIMAL values. The distinct types representing different currencies have been defined as DECIMAL. Your UDF will need to receive and return DOUBLE values, since this is the only data type provided by C that allows the representation of a DECIMAL value without losing the decimal precision. Thus, your UDF should be defined as follows:
CREATE FUNCTION CDN_TO_US_DOUBLE(DOUBLE) RETURNS DOUBLE
EXTERNAL NAME '/u/finance/funcdir/currencies!cdn2us'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
FENCED
The exchange rate between Canadian and U.S. dollars may change between two invocations of the UDF, so you declare it as NOT DETERMINISTIC.
The question now is, how do you pass Canadian dollars to this UDF and get U.S. dollars from it? The Canadian dollars must be cast to DECIMAL values. The DECIMAL values must be cast to DOUBLE. You also need to have the returned DOUBLE value cast to DECIMAL and the DECIMAL value cast to U.S. dollars.
Such casts are performed automatically by DB2 anytime you define sourced UDFs, whose parameter and return type do not exactly match the parameter and return type of the source function. Therefore, you need to define two sourced UDFs. The first brings the DOUBLE values to a DECIMAL representation. The second brings the DECIMAL values to the distinct type. That is, you define the following:
CREATE FUNCTION CDN_TO_US_DEC (DECIMAL(9,2)) RETURNS DECIMAL(9,2)
SOURCE CDN_TO_US_DOUBLE (DOUBLE)
CREATE FUNCTION US_DOLLAR (CANADIAN_DOLLAR) RETURNS US_DOLLAR
SOURCE CDN_TO_US_DEC (DECIMAL())
Note that an invocation of the US_DOLLAR function as in US_DOLLAR(C1), where C1 is a column whose type is Canadian dollars, has the same effect as invoking:
US_DOLLAR (DECIMAL(CDN_TO_US_DOUBLE (DOUBLE (DECIMAL (C1)))))
That is, C1 (in Canadian dollars) is cast to decimal which in turn is cast to a double value that is passed to the CDN_TO_US_DOUBLE function. This function accesses the exchange rate file and returns a double value (representing the amount in U.S. dollars) that is cast to decimal, and then to U.S. dollars.
A function to convert euros to U.S. dollars would be similar to the example above:
CREATE FUNCTION EURO_TO_US_DOUBL(DOUBLE)
RETURNS DOUBLE
EXTERNAL NAME '/u/finance/funcdir/currencies!euro2us'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
FENCED
CREATE FUNCTION EURO_TO_US_DEC (DECIMAL(9,2))
RETURNS DECIMAL(9,2)
SOURCE EURO_TO_US_DOUBL (DOUBLE)
CREATE FUNCTION US_DOLLAR(EURO) RETURNS US_DOLLAR
SOURCE EURO_TO_US_DEC (DECIMAL())
Suppose you want to know which products sold more in the US than in Canada and Germany for the month of July, 1999 (7/1999):
SELECT US.PRODUCT_ITEM, US.TOTAL
FROM US_SALES AS US, CANADIAN_SALES AS CDN, GERMAN_SALES AS GERMAN
WHERE US.PRODUCT_ITEM = CDN.PRODUCT_ITEM
AND US.PRODUCT_ITEM = GERMAN.PRODUCT_ITEM
AND US.TOTAL > US_DOLLAR (CDN.TOTAL)
AND US.TOTAL > US_DOLLAR (GERMAN.TOTAL)
AND US.MONTH = 7
AND US.YEAR = 1999
AND CDN.MONTH = 7
AND CDN.YEAR = 1999
AND GERMAN.MONTH = 7
AND GERMAN.YEAR = 1999
Because you cannot directly compare US dollars with Canadian dollars or euros, you use the UDF to cast the amount in Canadian dollars to US dollars, and the UDF to cast the amount in euros to US dollars. You cannot cast them all to DECIMAL and compare the converted DECIMAL values because the amounts are not monetarily comparable. That is, the amounts are not in the same currency.
Suppose you have defined a sourced UDF on the built-in SUM function to support SUM on euros:
CREATE FUNCTION SUM (EUROS)
RETURNS EUROS
SOURCE SYSIBM.SUM (DECIMAL())
You want to know the total of sales in Germany for each product in the year of 1994. You would like to obtain the total sales in US dollars:
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM GERMAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
You could not write SUM (us_dollar (total)), unless you had defined a SUM function on US dollar in a manner similar to the above.
Suppose you want to store the form filled by a new applicant into the database. You have defined a host variable containing the character string value used to represent the filled form:
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS CLOB(32K) hv_form;
EXEC SQL END DECLARE SECTION;
/* Code to fill hv_form */
INSERT INTO APPLICATIONS
VALUES (134523, 'Peter Holland', CURRENT DATE, :hv_form)
You do not explicitly invoke the cast function to convert the character string to the distinct type personal.application_form because DB2 lets you assign instances of the source type of a distinct type to targets having that distinct type.
If you want to use the same statement given in Example: Assignments Involving Distinct Types in dynamic SQL, you can use parameter markers as follows:
EXEC SQL BEGIN DECLARE SECTION;
long id;
char name[30];
SQL TYPE IS CLOB(32K) form;
char command[80];
EXEC SQL END DECLARE SECTION;
/* Code to fill host variables */
strcpy(command,"INSERT INTO APPLICATIONS VALUES");
strcat(command,"(?, ?, CURRENT DATE, CAST (? AS CLOB(32K)))");
EXEC SQL PREPARE APP_INSERT FROM :command;
EXEC SQL EXECUTE APP_INSERT USING :id, :name, :form;
You made use of DB2's cast specification to tell DB2 that the type of the parameter marker is CLOB(32K), a type that is assignable to the distinct type column. Remember that you cannot declare a host variable of a distinct type type, since host languages do not support distinct types. Therefore, you cannot specify that the type of a parameter marker is a distinct type.
Suppose you have defined two sourced UDFs on the built-in SUM function to support SUM on US and Canadian dollars, similar to the UDF sourced on euros in Example: Sourced UDFs Involving Distinct Types:
CREATE FUNCTION SUM (CANADIAN_DOLLAR)
RETURNS CANADIAN_DOLLAR
SOURCE SYSIBM.SUM (DECIMAL())
CREATE FUNCTION SUM (US_DOLLAR)
RETURNS US_DOLLAR
SOURCE SYSIBM.SUM (DECIMAL())
Now suppose your supervisor requests that you maintain the annual total sales in US dollars of each product and in each country, in separate tables:
CREATE TABLE US_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
CREATE TABLE GERMAN_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
CREATE TABLE CANADIAN_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
INSERT INTO US_SALES_94
SELECT PRODUCT_ITEM, SUM (TOTAL)
FROM US_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
INSERT INTO GERMAN_SALES_94
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM GERMAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
INSERT INTO CANADIAN_SALES_94
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM CANADIAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
You explicitly cast the amounts in Canadian dollars and euros to US dollars since different distinct types are not directly assignable to each other. You cannot use the cast specification syntax because distinct types can only be cast to their own source type.
Suppose you would like to provide your American users with a view containing all the sales of every product of your company:
CREATE VIEW ALL_SALES AS
SELECT PRODUCT_ITEM, MONTH, YEAR, TOTAL
FROM US_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL)
FROM CANADIAN_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL)
FROM GERMAN_SALES
You cast Canadian dollars to US dollars and euros to US dollars because distinct types are union compatible only with the same distinct type. Note that you have to use the functional notation to cast between distinct types since the cast specification only lets you cast between distinct types and their source types.