RE: Using SQL to check for duplicate records

[rob@xxxxxxxxx]

Or store it into a Query Management query and then it's just
STRQMQRY QMQRY(MYQUERY) OUTPUT(*PRINT)

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




rob@xxxxxxxxx 
Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
01/09/2004 03:47 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Using SQL to check for duplicate records






STRSQL
F13
1. Change session attributes
SELECT output  . . . . . . . .   1              1=Display, 2=Printer
                                                3=File 
WITH 
  TEMPTABLE (IPROD) AS 
    (SELECT IPROD 
       FROM IIM 
       GROUP BY IPROD 
       HAVING COUNT(*)>1) 
SELECT TEMPTABLE.IPROD, RRN(A) 
  FROM TEMPTABLE, IIM A 
  WHERE TEMPTABLE.IPROD = A.IPROD
....+....1....+....2....+....3....+..
IPROD                      RRN ( A ) 
A                                  1 
A                                  2 
********  End of data  ******** 

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




"Booth Martin" <Booth@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/09/2004 03:39 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Using SQL to check for duplicate records






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
 
 
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.