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

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

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

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