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

Try using a CTE:
With A1A2 as (Select * from A1 join A2 on xx = yy)
Select * from EE exception join A1A2 on EEPOL# = AAPOL#


Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3









----- Original Message ----
From: Dan <dan27649@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, April 2, 2008 1:50:49 PM
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