Example: Comparisons involving UDTs

 

Suppose that you want to know which products had higher sales in the U.S. than in Canada and Germany for the month of March 2003.

Issue the following SELECT statement:

     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 = 3 
       AND US.YEAR  = 2003 
       AND CDN.MONTH = 3 
       AND CDN.YEAR  = 2003 
       AND GERMAN.MONTH = 3 
       AND GERMAN.YEAR  = 2003

Because you cannot directly compare U.S. dollars with Canadian dollars or Euros, you use the UDF to cast the amount in Canadian dollars to U.S. dollars, and the UDF to cast the amount in Euros to U.S. dollars. You cannot cast them all to DECIMAL and compare the converted DECIMAL values because the amounts are not monetarily comparable as they are not in the same currency.

 

Parent topic:

Examples: Using UDTs