× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Pete, 

You want to copy only the record data from the ppay408a file, but since you
joined to another file, you're getting colummns appended to your result.
You need to change "Select *" to reference only the columns from ppay408a...

insert into ptmp408a
select ppay408a.* from ppay408a a inner join                   
(select tmlssn,tmljob,tmlped,tmldat  from ppay408a    
group by tmlssn,tmljob,tmlped,tmldat                  
having count(*) > 1) as dups                          
on a.tmlssn = dups.tmlssn                             
and a.tmljob = dups.tmljob                            
and a.tmlped = dups.tmlped                            
and a.tmldat = dups.tmldat

hope this helps.....

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Pete Helgren [mailto:pete@xxxxxxxxxx]
Sent: Friday, May 21, 2004 8:51 PM
To: Midrange-l@xxxxxxxxxxxx
Subject: Hairy SQL issue with duplicate (almost) records


This is the classic duplicate record issue where my customer posted data
twice and now has some duplicate data in a file.  The file is keyed such
that a "duplicate" really doesn't occur because there is a line number in
the record that can be incremented by the RPG program when a duplicate is
entered (there are reasons for this..which has back back to haunt me).

So  I have a table (ppay408a) that looks like:

tmlssn,tmljob,tmlped,tmldat, tmllin, etc

A "duplicate" is when the first four columns have identical values and the
line number (tmllin) is different.  So I wrote an sql statement that shows
me the duplicate records:

select * from ppay408a a inner join                   
(select tmlssn,tmljob,tmlped,tmldat  from ppay408a    
group by tmlssn,tmljob,tmlped,tmldat                  
having count(*) > 1) as dups                          
on a.tmlssn = dups.tmlssn                             
and a.tmljob = dups.tmljob                            
and a.tmlped = dups.tmlped                            
and a.tmldat = dups.tmldat

Sweet! All my duplicated records are there.  So I want to write them to a
file:

so I change the statement to be:

insert into ptmp408a
select * from ppay408a a inner join                   
(select tmlssn,tmljob,tmlped,tmldat  from ppay408a    
group by tmlssn,tmljob,tmlped,tmldat                  
having count(*) > 1) as dups                          
on a.tmlssn = dups.tmlssn                             
and a.tmljob = dups.tmljob                            
and a.tmlped = dups.tmlped                            
and a.tmldat = dups.tmldat

And I get an SQL error (SQL0117) saying "Statement contains wrong number of
values."

Is that because of the subselect?  Something else?  ptmp408a was created
using the crtdupobj command so the tables are identical, aren't they?

Second question?  Is there a better way to go about this (without writing a
program to do it...which is my alternative...)

Thanks,

Pete Helgren
Value Added Software,Inc.
801.581.1154 x202



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.