This article describes what to look for if you are encountering a DB005220 message in a UNIONed SELECT statement.
DB005220: The number of output columns must be identical for each SELECT definition within a UNION operation.
Environment
CA IDMS, all supported releases.
Cause
The cause of a DB005220 is a SELECT like this:
SELECT COL1, COL2, COL3 FROM TAB1 UNION SELECT COL1, COL2 FROM TAB2
The first SELECT has three columns and the second SELECT has only two. In a UNIONed query, all of the SELECTs must have the same number of columns.
Other things to look for... If a comma is missing from one of the column lists, then that individual select may still be valid on its own but cause the DB005220. For example:
SELECT COL1, COL2, COL3 FROM TAB1 UNION SELECT COL1, COL2 COL3 FROM TAB2
The above query will be interpreted as this (because the AS is optional):
SELECT COL1, COL2, COL3 FROM TAB1 UNION SELECT COL1, COL2 AS COL3 FROM TAB2
In the second SELECT, COL2 will have an alias of "COL3" and COL3 won't be in the SELECT at all, and the entire query will fail with DB005220.
Another possibility is that a comma is positioned beyond the 72-column boundary and is therefore not read in as part of the query.
Resolution
Correct the query so that all SELECTs have the same number of columns.