Re: the last instance of a record

I discovered *updadd about a 1-1/2 years ago, and I don't know when it
was added.

this is a one shot deal, so I have no concerns with compatability.

thanks,

On 3/19/06, Steve Richter <stephenrichter@xxxxxxxxx> wrote:
> On 3/19/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> > I've got a file with 2 or 3 years worth of history in it.  the key
> > fields are not unique, so duplicates abound.
> >
> > I want to extract the last instance (by rrn) of each key value.
> >
> > will this work?
> >
> > copy the file (by rrn) to a sequencial file (this ?might? not be necessary?)
> >
> > create an empty duplicate file with unique keyword specified.
> >
> > cpyf to the empty file using *UPDADD
> >
> > sound kosher?
>
> looks good. I never knew of mbropt(*updadd). When was that option
> added to the CPYF command?  Does CPYF only look to the keys of the
> tofile to determine that a record is a duplicate? ( as opposed to
> examining all logicals of the tofile )
>
> To maintain compatibility with the p5 I would do this as an sql procedure:
>   - insert all the columns of FROMFILE + a new column ( RRNCOL ) that
> is assigned the RRN of each row into a temporary table T1.
>   - insert a grouping of T1 on the key columns with a max(RRNCOL) into
> a temporary table T2.  T2 is now uniquely keyed on the key columns.
>   - Join from T2 back to T1 on the key columns and the RRNCOL column.
> This gives you all the columns of T1.  Insert all the T1 columns
> except for RRNCOL into a resulting table that is created like the
> FROMFILE.
>
> -Steve
>
> --
> 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.
>
>