RE: sql equivalent to chain and readpe

[Justin Taylor <JUSTIN@xxxxxxxxxxxxx>]

I'm pretty sure he's asking about Microsoft T-SQL (rather than Db2), and I don't think your code is valid in T-SQL.

-----Original Message-----
From: Craig Richards [mailto:craig@xxxxxxxxxxxxxxxx] 
Sent: Tuesday, October 29, 2019 7:56 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sql equivalent to chain and readpe

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