How can I treat a UNION query as a sub query

Question

I have a set of tables that are logically one table split into pieces for performance reasons. I need to write a query that effectively joins all the tables together so I use a single where clause of the result. I have successfully used a UNION on the result of using the WHERE clause on each subtable explicitly as in the following

SELECT * FROM FRED_1 WHERE CHARLIE = 42
UNION 
SELECT * FROM FRED_2 WHERE CHARLIE = 42
UNION 
SELECT * FROM FRED_3 WHERE CHARLIE = 42

but as there are ten separate subtables updating the WHERE clause each time is a pain. What I want is something like this

SELECT * 
FROM (
    SELECT * FROM FRED_1 
    UNION 
    SELECT * FROM FRED_2 
    UNION 
    SELECT * FROM FRED_3) 
WHERE CHARLIE = 42

If it makes a difference the query needs to run against a DB2 database.

Here is a more comprehensive (sanitised) version of what I need to do.

select * 
from ( select * from FRD_1 union select * from FRD_2 union select * from FRD_3 ) as FRD, 
     ( select * from REQ_1 union select * from REQ_2 union select * from REQ_3 ) as REQ, 
     ( select * from RES_1 union select * from RES_2 union select * from RES_3 ) as RES 
where FRD.KEY1 = 123456
  and FRD.KEY1 = REQ.KEY1
  and FRD.KEY1 = RES.KEY1
  and REQ.KEY2 = RES.KEY2

NEW INFORMATION:

It looks like the problem has more to do with the number of fields in the union than anything else. If I greatly restrict the fields I can get most of the syntax variations below working. Unfortunately, restricting the fields so much means the resulting query, while potentially useful, is not giving me the result I wanted. I've managed to get an additional 3 fields from one of the tables in addition to the 2 keys. Any more than that and the query fails.

Answer 1

I believe you have to give a name to your subquery result. I don't know db2 so I'm taking a shot in the dark, but I know this works on several other platforms.

SELECT * 
FROM (
    SELECT * FROM FRED_1 
    UNION 
    SELECT * FROM FRED_2 
    UNION 
    SELECT * FROM FRED_3) AS T1
WHERE CHARLIE = 42

Answer 2

If the logical implementation is a single table but the physical implementation is multiple tables then how about creating a view that defines the logical model.

CREATE VIEW VW_FRED AS 
SELECT * FROM FRED_1 
UNION    
SELECT * FROM FRED_2 
UNION    
SELECT * FROM FRED_3

then it's a simple matter of

SELECT * FROM VW_FRED WHERE CHARLIE = 42

Again, I'm not familiar with db2 syntax but this gives you the general idea.