DISTINCT

[K.Forsythe]

I think Group By would solve that problem - typically you would use that in conjunction with something else like SUM(), but I believe with would work here.

[bibarnes@yahoo.com]

DISTINCT

Didn't work either, but thanks for the response. What I find odd is that the ability to do what I indicated was able to be done with OPNQRYF and not SQL. I was under the impression that OPNQRYF used SQL beneath the covers. I checked with one of our senior server types and he couldn't think of a way to do it with SEQUEL Server. Oh Well! The important thing was that I was able to do it without writing a program to do it in. Thanks again for the response. Bill

[K.Forsythe]

DISTINCT

I think you could use your original statement to create a list of distinct values and then join that to a select of the next four columns assuming that you have a way to uniquely link the records. What do you think? Kevin

[Guest.Visitor]

DISTINCT

Bill Barnes wrote: > I am trying to clean up data and move some old legacy data files to > SQL Tables. I created one with most of the fields from the legacy and > ran an sql statement as follows > > delete from bbarnes.gpserror; > Insert into bbarnes.gpserror > select distinct ahstor, > ahtrn@, > ahtype, > ahcode, > COALESCE(CASE > when ahgrad = 'SU' then 1 > when ahgrad = 'UN' then 2 > when ahgrad = 'UP' then 6 > when ahgrad = 'DI' then 4 > when ahgrad = 'KE' then 5 > when ahgrad = ' ' then 0 > END,0) > from bbarnes.gsptah > > It worked great. Then I realized I needed 4 additional fields so I > added them to the select statement. > > ahprc1, > ahprc2, > ahgall, > ahgal2 > > The GPSERROR table has a primary key of STORE,TXDATE,ERRTYPE,ERRCODE. > > I got duplicates. I don't know what your data looks like, but this doesn't really surprise me since I would imagine that the combination of the other 4 fields could very likely cause duplicate keys. My concern in your successfully using OpnQryF is that you may actually be losing some valid record combinations. Here's one way to determine where your problems are: Select ahstor, ahtrn@, ahtype, ahcode, ahgrad, count(*) From bbarnes.gsptah Group by ahstor, ahtrn@, ahtype, ahcode, ahgrad Having Count(*) > 1 Bill

[bibarnes@yahoo.com]

DISTINCT

I am trying to clean up data and move some old legacy data files to SQL Tables. I created one with most of the fields from the legacy and ran an sql statement as follows delete from bbarnes.gpserror; Insert into bbarnes.gpserror select distinct ahstor, ahtrn@, ahtype, ahcode, COALESCE(CASE when ahgrad = 'SU' then 1 when ahgrad = 'UN' then 2 when ahgrad = 'UP' then 6 when ahgrad = 'DI' then 4 when ahgrad = 'KE' then 5 when ahgrad = ' ' then 0 END,0) from bbarnes.gsptah It worked great. Then I realized I needed 4 additional fields so I added them to the select statement. ahprc1, ahprc2, ahgall, ahgal2 The GPSERROR table has a primary key of STORE,TXDATE,ERRTYPE,ERRCODE. I got duplicates. What I would up doing was using OPNQRYF with the appropriate fields listed as keys and unique and then did a CPYFRMQRYF. Worked great. the question is, is there a SIMPLE way to do this within SQL? TIA Bill

[bibarnes@yahoo.com]

DISTINCT

In checking the "problem records" were from about 3 years in the past. I didn't care about the data in the 4 additional fields from that far back. The important thing was that we had the error recorded. I really appreciate all the suggestions. Even the ones that didn't work taught me something I didn't know before. I am living proof that you can teach an "old dog" new tricks. Thanks Bill