I have two objects which represent an inheritance.
Employee inherits from Person. To represent this, I have two tables: TPerson and TEmployee. I want to SELECT from both tables at the same time using the UNION ALL.
I want to do a UNION SELECT from both tables in the one query.
The problem is that the UNION syntax means that you must have the same number of columns in both tables.
There are some columns in TEmployee that are not in TPerson. For example suppose column EmployeeNumber is not in TPerson.
In oracle I can do:

SELECT EmployeeNumber, x1, x2, x3
FROM TEMPLOYEE
UNION ALL
SELECT null as EmployeeNumber, x1, x2, x3
FROM TPERSON.

However I can't do this in DB2, I try to do:

SELECT EmployeeNumber, x1, x2, x3
FROM TEMPLOYEE
UNION ALL
SELECT cast(null as INT), x1, x2, x3
FROM TPERSON

but this does not work.

I get: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703 i.e. An undefined column, attribute, or parameter name was detected.

Any ideas?

Are you sure EmployeeNumber is INT?

Check the spelling.

Can you give the DLL (create table) scripts for both tables ?

you might also try


SELECT EmployeeNumber, x1, x2, x3
FROM TEMPLOYEE
UNION ALL
SELECT NULLIF( 1 , 1 ), x1, x2, x3
FROM TPERSON