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