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



John,

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

Follow-Ups:
Replies:

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.