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

Dan,

Just a quick followup to my earlier note. You'll want to qualify your
"select * from EE", otherwise you'll also pick up nulls for the fields in A1
and A2.


with A as (select * from A1 union
select * from A2)
select ee.* from EE ee exception join A on EEPOL#=AAPOL#


with notA1 as (select ee.* from EE ee exception join A1 on EEPOL#=AAPOL#)
select notA1.* from notA1 exception join A2 on EEPOL#=AAPOL#


Enjoy!
Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Richard Casey
Sent: Wednesday, April 02, 2008 2:00 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL exception join: Left side now needs data combined from
twotables


Dan,

You could try using a common table expression (CTE) to combine A1 and A2.

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

If the field names are different in A1 and A2, you would have to deal with
that.

Depending on the size of the A1 and A2 tables, you might use a CTE to come
up with a list of EE records that aren't in A1 and then use an exception
join between A2 and that CTE.

with notA1 as (select * from EE exception join A1 on EEPOL#=AAPOL#)
select * from notA1 exception join A2 on EEPOL#=AAPOL#

Have fun!
Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
Sent: Wednesday, April 02, 2008 1:51 PM
To: Midrange Systems Technical Discussion
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
--

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.4/1355 - Release Date: 4/1/2008
5:37 PM