Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser
Last active March 26, 2024 00:58
Show Gist options
  • Save BirgittaHauser/afffab22887b34e292af01ae07c92413 to your computer and use it in GitHub Desktop.
Save BirgittaHauser/afffab22887b34e292af01ae07c92413 to your computer and use it in GitHub Desktop.
-- Question in an Forum: I have a table/file which has duplicate records (Lets say there are 10 fields in a record).
-- Out of which, if 5 fields put together are same across more than 1 record, then it is considered as a duplicate).
-- I need to delete those duplicate records alone using a SQL statement embedded in a SQLRPGLE program.
-- 1. Solution with a Cursor in embedded SQL (using a Common Table Expression for determining the duplicate rows!)
**Free
//---------------------------------------------------------------------------------------------
CTl-Opt DatFmt(*ISO) Option(*NoDebugIO);
//---------------------------------------------------------------------------------------------
DCL-S GblRRN Int(20);
//---------------------------------------------------------------------------------------------
Exec SQL Set Option Commit=*NONE, DatFmt=*ISO;
//---------------------------------------------------------------------------------------------
*INLR = *On;
Monitor;
Exec SQL
Declare CsrDupRow Cursor For
With x as (Select Key1, Key2, Key3, Key4, Key5, Max(RRN(a)) MaxRRN
From MyTable a
Group By Key1, Key2, Key3, Key4, Key5
Having Count(*) > 1)
Select RRN(b)
from x join MyTable b on x.Key1 = b.Key1
and x.Key2 = b.Key2
and x.Key3 = b.Key3
and x.Key4 = b.Key4
and x.Key5 = b.Key5
and rrn(b) <> MaxRRN;
Exec SQL Open CsrDupRow;
If SQLCODE < *Zeros;
//Handle Error
Return;
EndIf;
DoU 1=0;
Exec SQL Fetch Next from CsrDupRow into :GblRRN;
If SQLCODE < *Zeros;
//Handle Error
Leave;
ElseIf SQLCODE = 100;
Leave;
EndIf;
Exec SQL Delete from MyTable b Where RRN(b) = :GblRRN;
If SQLCODE < *Zeros;
//Handle Error
EndIf;
EndDo;
Exec SQL Close CsrDupRow;
On-Error;
//Handle Error
EndMon;
Return;
-- 2. Solution: Handle everything in a DELETE Statement
-- Unfortunately Common Table Expressions cannot be used within a DELETE Statement, but nested sub-Selects can:
Delete from MyTable c
Where rrn(c) in (Select RRN(b) rrnDlt
from (Select Key1, Key2, Key3, Key4, Key5,
Max(RRN(a)) MaxRRN
from MyTable a
Group By Key1, Key2, Key3, Key4, Key5) x
join MyTable b on x.Key1 = b.Key1
and x.Key2 = b.Key2
and X.Key3 = b.Key3
and x.Key4 = b.Key4
and x.Key5 = b.Key5
and RRN(b) <> MaxRRN);
@GlennGundermann
Copy link

Are you missing Key4, Key5 on line 68?

@BirgittaHauser
Copy link
Author

BirgittaHauser commented Oct 17, 2022 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment