17

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.

Michael Rutherfurd
  • 13,815
  • 5
  • 29
  • 40
  • 3
    Does that syntax produce an error? If you use UNION ALL rather than UNION it might work OK. Then you put the unions into a view and you've reinvented partitioning... – araqnid Mar 09 '11 at 00:32
  • You realize this "divide-the-table" strategy is an identified pessimization technique? – dkretz Mar 09 '11 at 00:43
  • I actually had to look up what pessimization meant :-) Having said that, the divide the table idea is part of an application about 10 years old so I can't do much about it. – Michael Rutherfurd Mar 09 '11 at 04:06

5 Answers5

38

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
Infotekka
  • 10,307
  • 2
  • 20
  • 17
  • This works in the simple case of one group of tables (union) but doesn't work if I want to join to another similar group T2 – Michael Rutherfurd Mar 09 '11 at 04:52
  • Does your original query produce an error or is it just not returning the results you wanted? – Infotekka Mar 09 '11 at 20:21
  • I get the error SQL0136, SQLCODE -136, SQLSTATE 54005 which means "ORDER BY or GROUP BY columns too long". Problem is I get this even though I don't have an ORDER BY or GROUP BY in my query. I do, however, have 30 tables (3 groups of 10, each group related to the next by key (T1.KEY = T2.KEY and T2.KEY = T3.KEY and T2.KEY2 = T3.KEY2)) – Michael Rutherfurd Mar 09 '11 at 23:20
  • can you post your SQL that you are trying to join into? I do that (joining into a query result) all the time on a DB2 and don't have a problem. – Leslie Mar 10 '11 at 16:04
  • @Leslie Added more details at bottom of question – Michael Rutherfurd Mar 11 '11 at 00:26
  • This worked for me on IBM DB2 SQL, but it should be noted as mentioned in the first comment above: it was for a simple from-subquery which contained a union of multiple tables, with no joins either inside or outside the subquery. – spinjector Nov 30 '17 at 19:10
  • BTW - That was for DB2 v6. – spinjector Nov 30 '17 at 20:45
3

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.

Dave Barker
  • 6,303
  • 2
  • 24
  • 25
  • This would be great, if I was allowed to create views... locally DB2 installed on mainframe "needs" DB2 group to "manage" all modifications even for DEV. – Michael Rutherfurd Mar 09 '11 at 05:23
1
with 
FRD as ( select * from FRD_1 union select * from FRD_2 union select * from FRD_3 ), 
REQ as ( select * from REQ_1 union select * from REQ_2 union select * from REQ_3 ), 
RES as ( select * from RES_1 union select * from RES_2 union select * from RES_3 )
SELECT * from FRD, REQ, RES 
WHERE FRD.KEY1 = 123456
and FRD.KEY1 = REQ.KEY1
and FRD.KEY1 = RES.KEY1
and REQ.KEY2 = RES.KEY2
Peter Miehle
  • 5,984
  • 2
  • 38
  • 55
  • I get SQLCODE -136 SQLSTATE 54005 (The sort key is too long, or has too many columns.) as soon as I include any unions. This syntax does work if I don't specify unions. – Michael Rutherfurd Mar 12 '11 at 00:03
0

I'm not familiar with DB2 syntax but why aren't you doing this as an INNER JOIN or LEFT JOIN?

SELECT * 
  FROM FRED_1
 INNER JOIN FRED_2
    ON FRED_1.Charlie = FRED_2.Charlie
 INNER JOIN FRED_3
    ON FRED_1.Charlie = FRED_3.Charlie
 WHERE FRED_1.Charlie = 42

If the values don't exist in FRED_2 or FRED_3 then use a LEFT/OUTER JOIN. I'm assuming that FRED_1 is a master table, and if a record exists then it will be in this table.

fragilewindows
  • 1,394
  • 1
  • 15
  • 26
Dave Barker
  • 6,303
  • 2
  • 24
  • 25
0

maybe:

SELECT * FROM 
(select * from FRD_1 
union 
select * from FRD_2 
union 
select * from FRD_3) FRD
INNER JOIN (select * from REQ_1 union select * from REQ_2 union select * from REQ_3) REQ
  on FRD.KEY1 = REQ.KEY1
INNER JOIN (select * from RES_1 union select * from RES_2 union select * from RES_3) RES
  on FRD.KEY1 = RES.KEY1
WHERE FRD.KEY1 = 123456 and REQ.KEY2 = RES.KEY2
Leslie
  • 3,604
  • 7
  • 38
  • 53