Performing assignments involving different distinct types

This section describes how to perform assignments involving different distinct types.

About this task

Suppose you have defined two sourced UDFs on the built-in SUM function to support SUM on U.S. and Canadian dollars:

     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 U.S. dollars of each product and in each region, 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 convert 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.