RE: Using SQL to check for duplicate records

Matching Records would be a natural here too...

On Tue, 6 Jan 2004 15:43:43 -0600 (Central Standard Time), "Booth Martin"
<Booth@xxxxxxxxxxxx> said:
> ahhhhh the joys of using RPG the way it was intended...
> 
> Such an easy problem with an L1 break and using the  INFDS for the
> input/primary file.
>  
>  
>  
> ---------------------------------------------------------
> Booth Martin   http://www.MartinVT.com
> Booth@xxxxxxxxxxxx
> ---------------------------------------------------------
>  
> -------Original Message-------
>  
> From: Midrange Systems Technical Discussion
> Date: 1/6/2004 2:40:01 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Using SQL to check for duplicate records
>  
> Gord,
>  
> That solution doesn't display the RRN's of the duplicates.
>  
> Enforcing RI programmatically is a joke.  When you have to merge
> divisions, etc, you're going to have file maintenance outside of the one
> 5250 file maintenance program.
>  
> Rob Berendt
> --
> "All creatures will make merry... under pain of death."
> -Ming the Merciless (Flash Gordon)
>  
>  
>  
>  
> Gord Royle <GRoyle@xxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/06/2004 01:30 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
>  
>  
>  
>  
>  
>  
> Hi Rob
>  
> I've done this before (on BPCS).
>  
> Select iprod, count(iprod) from lib.iim
> group by iprod
> having count(iprod) > 1
>  
> Yhis is fast !!!
>  
> But - I'm curious. I know BPCS inforces RI programatically - so - how can
> you have duplicates in IIM? Don't tell me - Someone went in outside of
> BPCS
> control <BG>.
>  
> Gord
>  
> -----Original Message-----
> From: Fisher, Don [mailto:Dfisher@xxxxxxxxxxxxxxxxx]
> Sent: Tuesday, January 06, 2004 11:06 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Using SQL to check for duplicate records
>  
>  
> I don't know if it's more efficient, but try:
> SELECT IPROD FROM IIM group by IPROD
>     Having count(*) > 1
>  
> That will give you the IPROD values that are duplicated.  If you want the
> specific record numbers, you'll have to use the result set to join back
> to
> IIM.
>  
> Hope that helps.
>  
> Donald R. Fisher, III
> Project Manager
> Roomstore Furniture Company
> (804) 784-7600 extension 2124
> DFisher@xxxxxxxxxxxxx
>  
> <clip>
> Is there a more efficient way, using SQL, to check for duplicate records
> than the following?
> SELECT A.IPROD, RRN(A) FROM IIM A
> WHERE A.IPROD IN (
>   SELECT B.IPROD FROM IIM B
>   GROUP BY B.IPROD
>   HAVING COUNT(*) > 1)
> <clip>
> _______________________________________________
> 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.
>  
>  
> _______________________________________________
> 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.
> 
-- 
  
  michaelr_41@xxxxxxxxxxxxxx

-- 
http://www.fastmail.fm - Sent 0.000002 seconds ago