RE: SQL newbie question

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Shoot,

Wasn't thinking....that would work, but you'd end up with duplicates
since the contents of myfile aren't cleared.

Using a temporary file or STRQMQRY as Vernon suggested would be the
easiest method.  

If you really want to do it with SQL then something like so is what you
want:

Insert into myfile (
With allRecs as (Select <....> from myfile
                  Union distinct
                 Select <....> from f2
                  Union distinct
                 Select <....> from f3
                  Union distinct
                 Select <....> from f4
Select A.* 
from allRecs A
  exception join myfile M
    on a.fld1 = m.fld1
   and a.fld2 = m.fld2
   <....> 
) 


If there's a single keyed field you can use, to determine if the recs
are a duplicate then this is a litle easier to code:
Insert into myfile (
With allRecs as (Select <....> from myfile
                  Union distinct
                 Select <....> from f2
                  Union distinct
                 Select <....> from f3
                  Union distinct
                 Select <....> from f4
Select A.* 
from allRecs A
Where A.key not in (select key from myfile) 
) 


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Friday, November 11, 2005 9:58 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL newbie question

James,

Sure, just:
Insert into myfile (
Select <....> from myfile
Union distinct
Select <....> from f2
Union distinct
Select <....> from f3
Union distinct
Select <....> from f4
) 

The entire results set is built before any record is inserted.



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James H H Lampert
Sent: Thursday, November 10, 2005 8:28 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL newbie question

"Wilt, Charles" wrote:
> Assuming that the a key collision means the rest of the 
>record is the
> same too, then all you need is:
> Insert into myfile (
> Select <....>
> Union distinct
> Select <....>
> Union distinct
> Select <....> 
> )

Yes, but is there a convenient way to UNION what's going 
into a file with what's already there?

--
JHHL