Performing UNION operations on distinctly typed columns

This section describes how to perform UNION operations on distinctly typed columns.

About this task

Suppose you would like to provide your American users with a view containing all the sales of every product of your company:

     CREATE VIEW ALL_SALES AS 
       SELECT PRODUCT_ITEM, MONTH, YEAR, TOTAL 
       FROM US_SALES 
       UNION 
       SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL) 
       FROM CANADIAN_SALES 
       UNION 
       SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL) 
       FROM GERMAN_SALES

You cast Canadian dollars to US dollars and euros to US dollars because distinct types are union compatible only with the same distinct type. The preceding example makes use of the UDFs defined in Casting between distinct types to cast between the currencies, which results in the use of functional notation instead of a cast specification.