RE: Hairy SQL issue with duplicate (almost) records

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