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

[Jeff Young <cooljeff913@xxxxxxxxx>]

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