UNION — Derived Result Column Name
With most DBMSs, the result column name for a UNION select is simply the column names in the first select. However, DB2 UDB uses derived result column names (such as "1", "2", and so on) if the corresponding column names in the UNION are not the same. In such cases, column names used in the ORDER BY clause are treated as invalid references. For example, using column_a in the ORDER BY clause in the following statement is invalid in DB2 UDB, but valid in other DBMSs.
SELECT column_a FROM table_a
UNION
SELECT column_b FROM table_b
ORDER BY column_a ;
For optimum portability and to avoid problems with DB2 UDB, consider changing the second SELECT to use names that match previous SELECT statements:
SELECT column_b AS column_a FROM table_b ;
When using Terminal monitor or in reports where column names are significant, we recommend that you assign all expressions an explicit alias.
This also applies to queries that make use of derived tables.