Performing comparisons involving distinct types

This topic describes how to perform comparison involving distinct types.

About this task

Suppose you want to know which products sold more in the United States 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 U.S. dollars with Canadian dollars or euros, use the UDF to cast the amount in Canadian dollars to US dollars, and the UDF to cast the amount in euros to U.S. dollars. You should not 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.