Restrictions when using distinct types with UNION, EXCEPT, and INTERSECT
Db2 enforces strong typing of distinct types with UNION, EXCEPT, and INTERSECT. When you use these keywords to combine column values from several tables, the combined columns must be of the same types. If a column is a distinct type, the corresponding column must be the same distinct type.
Example: Suppose that you create a view that combines the
values of the US_SALES, EUROPEAN_SALES, and JAPAN_SALES tables. The
TOTAL columns in the three tables are of different distinct types.
Before you combine the table values, you must convert the types of
two of the TOTAL columns to the type of the third TOTAL column. Assume
that the US_DOLLAR type has been chosen as the common distinct type.
Because Db2 does not generate
cast functions to convert from one distinct type to another, two user-defined
functions must exist:
- A function called EURO_TO_US that converts values of type EURO to type US_DOLLAR
- A function called YEN_TO_US that converts values of type JAPANESE_YEN to type US_DOLLAR
SELECT PRODUCT_ITEM, MONTH, YEAR, TOTAL
FROM US_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, EURO_TO_US(TOTAL)
FROM EUROPEAN_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, YEN_TO_US(TOTAL)
FROM JAPAN_SALES;