RE: SQL issue with EXISTS not "working"

We are missing something in what you are attempting to do and we are
guessing at answers not knowing all of the details.
1) Is ITEM the only field in COMMON_FILE?
2) Are there multiple records in FILE_A with the same ITEM value?
2a) Are the multiple records in FILE_A complete duplicates or is the data
other than the ITEM on the record different?

Coming back to your original SQL, this analyzes and builds the entire
dataset from the "select * from FILE_A..." before it inserts anything. If
you have multiple records in FILE_A with the same ITEM value and ITEM does
not already exist in COMMON_FILE, all records with that ITEM will be
inserted into COMMON_FILE.

If there are multiple records in FILE_A that have the same ITEM value and
the other data on the record is identical, you can do "select distinct *
from FILE_A..." or use the UNION as mentioned by Vern as long as you are
adding records from FILE_B.

If there are multiple records in FILE_A that have the same ITEM value but
the other data on the record is different, you can't do what you are trying
to do without something to tell it which of the ITEM records you really want
since you only want one of them.

INSERT INTO COMMON_FILE
(SELECT * FROM FILE_A A1
WHERE NOT EXISTS (SELECT 1 FROM COMMON_FILE A2 WHERE A1.ITEM =
A2.ITEM))

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Darryl Freinkel
Sent: Thursday, October 23, 2014 2:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

@Vern,
COMON_FILE is empty to start with. I have tried
-- Select 1...
-- Select ITEM ...
-- Select * ...

all with the same result.

On Thu, Oct 23, 2014 at 1:20 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Yes, this would be true if he uses each statement to add to the common
file - so yeah, has to watch out, I guess

I was reading it that the common_file was a new thing - empty - in
this case, the UNION is fine, as there is nothing to check against.

Of course, there could be a UNION of the new stuff with the old - into
a work file, then CPYF into the production one - something like that.

I do suspect that the test against duplicates is not broad enough -
this usually requires testing against the entire record - all fields.

Guess we need to hear from Darryl what he is doing, whether adding to
existing or building something brand new.

Cheers
Vern


On 10/23/2014 11:54 AM, John R. Smith, Jr. wrote:

I'm reading his SQL to be that he wants to insert into COMMON_FILE
records that are not already in COMMON_FILE which this UNION does not
handle. If he has a matching record in COMMON_FILE and FILEA, he
will get a duplicate because you are not excluding what is already
there.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Vernon Hamberg
Sent: Thursday, October 23, 2014 12:50 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL issue with EXISTS not "working"

If he uses UNIONs even the duplicates in each file will be removed.

On 10/23/2014 11:37 AM, John R. Smith, Jr. wrote:

Are the duplicates records that are already in common_file or are
you getting duplicates from FILE_A? If the latter, try select
distinct * from...

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Darryl Freinkel
Sent: Thursday, October 23, 2014 11:56 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL issue with EXISTS not "working"

I need some help on this one.

I am merging 5 company records into 1 file and need to drop any

duplicates.

This is the statement:
INSERT INTO COMMON_FILE
(SELECT * FROM FILE_A A1
WHERE NOT EXISTS (SELECT 1 FROM COMMON_FILE A2 WHERE A1.ITEM =
A2.ITEM))

Problem:
The exists is not working and as a result the insert is inserting
duplicates.

What alternative ways are there to achieve this merge or what
mistake am I not seeing?

I have 5 similar SQL statements to run.

TIA

--
Darryl Freinkel
--
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.


--

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.

--
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.