RE: SQL exception join: Left side now needs data combined from two tables

CTE is a good option, as others have pointed out. You will want to specify
UNION ALL instead of UNION, to avoid DISTINCT processing.

Nested table expression you have tried should have worked as well, perhaps
with a slight modification to name (alias) the temp table (i.e. a1a2):

select * from EE exception join
(select * from A1 union ALL
select * from A2 ) a1a2 on EEPOL# = AAPOL#

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL exception join: Left side now needs data combined from two
tables

I have a query that uses an exception join. I.e.
select * from EE exception join A1 on EEPOL# = AAPOL#

Some of the data in A1 is now being split off into a new table A2, so data
that used to be in A1 but is now in A2 is being returned as a result in this
query. I need the right side of this join to have the rows from both A1 &
A2. Pseudo-code:
select * from EE exception join (A1||A2) on EEPOL# = A1POL#

I know I could use a temporary file to combine A1 & A2, use the temp file on
the right side, and call it done, but I'm trying to learn whether SQL has a
way to handle this. I thought of UNION, but don't think this will work for
an exception join. I tried:

select * from EE exception join
(select * from A1 union
select * from A2 ) on EEPOL# = AAPOL#

but no good.

Any ideas would be greatly appreciated.

TIA,
Dan