Example: Assignments involving different UDTs

Suppose that you have defined these sourced user-defined functions (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 QSYS2.SUM (DECIMAL()); 
  
     CREATE FUNCTION SUM (US_DOLLAR) 
       RETURNS US_DOLLAR 
       SOURCE QSYS2.SUM (DECIMAL());

Now suppose your supervisor requests that you maintain the annual total sales in U.S. dollars of each product and in each country, in separate tables:

 
     CREATE TABLE US_SALES_04 
       (PRODUCT_ITEM  INTEGER, 
        TOTAL         US_DOLLAR); 
  
     CREATE TABLE GERMAN_SALES_04 
       (PRODUCT_ITEM  INTEGER, 
        TOTAL         US_DOLLAR); 
  
     CREATE TABLE CANADIAN_SALES_04 
       (PRODUCT_ITEM  INTEGER, 
        TOTAL         US_DOLLAR); 
  
     INSERT INTO US_SALES_04 
       SELECT PRODUCT_ITEM, SUM (TOTAL) 
       FROM US_SALES 
       WHERE YEAR = 2004 
       GROUP BY PRODUCT_ITEM; 
  
     INSERT INTO GERMAN_SALES_04 
       SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL)) 
       FROM GERMAN_SALES 
       WHERE YEAR = 2004 
       GROUP BY PRODUCT_ITEM;
  
     INSERT INTO CANADIAN_SALES_04 
       SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL)) 
       FROM CANADIAN_SALES 
       WHERE YEAR = 2004 
       GROUP BY PRODUCT_ITEM;

You explicitly cast the amounts in Canadian dollars and Euros to U.S. dollars since different UDTs are not directly assignable to each other. You cannot use the cast specification syntax because UDTs can only be cast to their own source type.