Dan,
Instead of doing a UNION of A1 and A2, try this.
with notA1 as (select EE.* from EE exception join A1 on EEPOL=AAPOL)
select notA1.* from notA1 exception join A2 on EEPOL=AAPOL
The CTE will pick the records in EE that don't exist in A1. The main select
statement will then use that list and pick the records that don't exist in
A2.
Have fun!
Richard
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
Sent: Wednesday, April 02, 2008 4:30 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL exception join: Left side now needs data combined from
twotables
On Wed, Apr 2, 2008 at 2:16 PM, Elvis Budimlic <
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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#
Thanks to all!! CTE did the trick! Although I am a bit surprised at how
slowly the union of the two A1/A2 files takes to complete. A1 has over 1
million records, A2 approx. 5000. Seems like a CPYF the two files into a
temp file would have been a LOT faster. Will have to test this.
Elvis, it seems odd that UNION would, by default (DISTINCT), weed out
duplicates. Is that the SQL standard? In this case, I don't think it
affects this query, as all records between the two tables are unique.
Also, you are right about the nested table expression. It worked as you
suggested. The union appeared to run faster here than using the CTE. Neat
stuff!!!
- 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
midrange.com
