Re: Using SQL to delete "duplicate" record

[Luis Rodriguez <luisro58@xxxxxxxxx>]

BTW, in my code I'm assuming that all your dates are, at least, from the
year 2000..

Regards,

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



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

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