Re: Does SQL by chance currently support row look-behind and look-ahead?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I googled for "join row with previous row sql" and got this hit - 
https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement

It mentions lag and lead functions - these are OLAP functions - I 
wondered if they exist on the i. And they do -

LAG or LEAD Returns an expression value computed using rows prior to or 
following the current row.

These seem to have been added in 7.3 - yes, it is not in 7.2

This looks like just the thing - you do need something that sorts the 
results - a windowing thing - it's explained in the manual, 
https://www.ibm.com/docs/en/ssw_ibm_i_73/pdf/rbafzpdf.pdf, around page 184.

Enjoy!!
Vern

On 3/9/2023 6:36 PM, Javier Sanchez wrote:
> I have always liked the idea of doing something with the previous and/or
> the next-look-ahead row while you work with the current row.  I suspect
> that exists.  But not with external coding but with some kind of say:
>
> select col1, col2 + PREVIOUS_ROW(col1) from sometable where
> somecondition=true/false;  // whatever
>
> There may also be a kind of NEXT_ROW(col2) thing.
>
> This is of course a meta-statement of the idea.
>
> You may say I can write a UDF that returns the previous row col2 value, but
> it would be very difficult because of the nature of your table and your
> logic.
>
> It would be nice that this be an intrinsic DB2 functionality.  Do we have
> that already?
>
> Please your comments.
>
> TIA.
>
> Javier.