Re: sql equivalent to chain and readpe

[Craig Richards <craig@xxxxxxxxxxxxxxxx>]

Well, that might be too simple actually, it doesn't prove you had a 'REPAIR'

On Tue, 29 Oct 2019 at 12:55, Craig Richards <craig@xxxxxxxxxxxxxxxx> wrote:

> What about
>
> select status from file
> where ID = 21489 and Type < 'REPAIR'
> order by ID, Type, Date
> fetch first row only
>
> On Tue, 29 Oct 2019 at 12:49, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
> wrote:
>
> > I am converting a rpg program that uses a chain, followed by a readpe to
> > retrieve a value.
> >
> > I am converting this program to pull the same data from a sql server
> > table using Scott Klements jdbc methods.
> > I am looking at using the sql lag function, but that does not seem to be
> > what I need.
> >
> > Data looks like the following
> >
> > ID          TYPE               Date                             Status
> > 21489   RECHECK      20180928                   2
> > 21489   REGRADE      20180418                   2
> > 21489   REPAIR          20190102                    0
> >
> > Key in file is ID,TYPE,Date
> >
> > Chain looks for the REPAIR, then does a read prior equal on the ID to get
> > the previous status
> >
> > Is there a way I can do  the same thing using sql ?
> >
> > Thanks
> >
> > MIke
> >
> >
> >
> > NOTICE: This message, including any attachment, is intended as a
> > confidential and privileged communication. If you have received this
> > message in error, or are not the named recipient(s), please immediately
> > notify the sender and delete this message.
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com