Performing comparisons between distinct types and constants

Suppose you want to know which products sold more than U.S. $100 000.00 in the United States 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 U.S. dollars (that is, DECIMAL) directly, you have used the cast function provided by Db2® to cast from DECIMAL to U.S. dollars. You can also use the other cast function provided by Db2 (that is, the one to cast from U.S. 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 preceding query as:

     SELECT PRODUCT_ITEM 
       FROM   US_SALES 
       WHERE  TOTAL > CAST (100000 AS us_dollar) 
       AND    MONTH = 7 
       AND    YEAR  = 1999