Re: Using SQL to delete "duplicate" record

Sorry, forgot to add that iDate is an UDF written by Alan Campin.

If you wish, you can change it with:

(substr(digits(b.crtdat), 5, 2) || substr(digits(b.crtdat), 1, 4)) as
CreateDate

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Thu, Nov 4, 2010 at 9:47 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Alan,

I think this could work:

CREATE VIEW QTEMP/VF2 AS
WITH T1 AS(
SELECT A.*,
(Idate(crtdate)) as CreateDate
FROM QTEMP/File1 A
)
SELECT * FROM T1 A
WHERE EXISTS(
SELECT 1 FROM T1 B
WHERE A.Ord# = B.Ord# AND A.CreateDate < B.CreateDate
)
;

Check your data with a Select * from VF2, those are the records to be
deleted with a DELETE FROM VF2.

Warning!! Be sure to back up your data before any mass delete!!. Also,
this can be a little slow.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Thu, Nov 4, 2010 at 8:52 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Morning all
I have a file that is keyed, but I have multiple records with the same
key
that I need to delete but keep the latest record
Fortunately there is a create "date" on the file (really a 6 digit
numeric
field in mmddyy format)

here is my sql statement

Delete From FILE1 A
Where RRN(A) in
(Select RRN(B),
b.* From FILE1b, FILE1 C
Where B.ord# = C.ord# and
substr(digits(b.crtdat), 5, 2) || substr(digits(b.crtdat), 1, 4) <
substr(digits(c.crtdat), 5, 2) || substr(digits(c.crtdat), 1, 4))


Unfortunately I am receiving the following message
Subselect with more than one result column not valid.

I checked and confirmed that for the same Ord#, there are more than 2
records (5 or 6 in some cases)

Ord# is the only field that is the key. The other fields on the record
have
no bearing between the duplicate ord# (Hopefully that last sentence makes
sense)

Anyone have any ideas how I can circumvent this problem
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
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.