DB005220 in a SELECT with UNION

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

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.

Additional Information

SELECT