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



One question I have about the SQL solution is about printing the results.
When/how does one print the RRN of all duplicate records using only SQL?
 
Don't you do need to save the RRN number of the first record anyway?   Look
ahead would work if there were just one duplicate record, but a third record
would no longer have the first record's RRN number available would it?
 
 
---------------------------------------------------------
Booth Martin http://www.MartinVT.com
Booth@xxxxxxxxxxxx
---------------------------------------------------------
 
-------Original Message-------
 
From: Midrange Systems Technical Discussion
Date: 1/9/2004 2:15:35 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Using SQL to check for duplicate records
 
Booth:
 
The suggestion came out of trying to duplicate Rob's specific request where
each printed line of output showed the key value and the RRN for a record.
By using lookahead (which is still discussed in the RPG/400 Reference
accessed via V5R2 InfoCenter), you can know if the first record in an
L1-group is the only record in the group... that is, if lookahead works like
I've thought it should.
 
In any other RPG technique, when you've read the first record, how do you
know whether to print it or not? Well, you don't. You save the needed values
and read the next record. After reading the next record, you decide whether
to print the previous values or not. Then, you decide what to do with the
current record. And when you get the first record of a new group, what do
you do about the last record of the previous group?
 
Well, lookahead lets you take a sneak peek at the next record. No need to
save any values, no need to complicate the logic. Just make a direct
comparison for the first record of a group and the decision is made for
every record in the group, whether the group contains one record or 50000.
 
Two F-specs, three I-specs, two lines of C-specs, two lines of O-specs (add
headings for neatness). Together with the extraction of RRN, it's pretty
direct.
 
In SQL, just getting count(*) for the groups could take a chunk of
processing. I'm not sure it could be any more efficient in any other
language.
 
Tom Liotta
 
 

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.