×
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.
There are several ways to identify (and optionally delete) duplicate
records with SQL.
An approach that (albeit a little bit slow) allows you to easily check your
data could be as follows:
*CREATE VIEW MYLIB/MYVIEW AS *
* **WITH** T1 AS( *
*SELECT A.*,** RRN(A) AS RRNO *
*FROM MYLIB/MYFILE A *
*) *
*SELECT * FROM T1 A WHERE EXISTS( *
* SELECT 1 FROM T1 B *
* * WHERE A.ACCOUNT = B.account AND A.RRNO > B.RRNO
*)*
*;*
If you do a SELECT * FROM MYVIEW you would see only your duplicate records.
To delete them, use DELETE FROM MYVIEW (please backup beforehand!!).
Of course, an index over the accout column would help a lot.
Also, please remember that if you had defined your SQL with either a
PRIMARY KEY or a UNIQUE constraint referencing the Acct. number, this would
have been, as you say, an issue.
HTH,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
midrange.com
