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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Nice! I started October 1989 at Help Systems, tech support for Autotune 
and other products. S/38 and AS/400 - have to at least try to catch up 
to you, man!

On 3/9/2023 8:19 PM, Roger Harman wrote:
> 14 months, dude.  Best job I've ever had!
>
> As much as I thoroughly enjoyed coding, especially the last job for 7+ years, I haven't missed it at all.
> 37 years starting on S/38.
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via MIDRANGE-L
> Sent: Thursday, March 9, 2023 6:11 PM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
> Subject: Re: Does SQL by chance currently support row look-behind and look-ahead?
>
> Say it isn't so, Roger!!! Retire? Nooooooooooooo!!
>
> :)
>
> On 3/9/2023 8:05 PM, Roger Harman wrote:
> > I soooo wanted to use LAG() and LEAD()for a couple of tasks.  Sadly, we were on 7.2 and I retired before they upgraded - if they even have now.
> >
> > Dang....
> >
> >
> > Roger Harman
> > COMMON Certified Application Developer - ILE RPG on IBM i on Power
> >
> > -----Original Message-----
> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via MIDRANGE-L
> > Sent: Thursday, March 9, 2023 5:41 PM
> > To: midrange-l@xxxxxxxxxxxxxxxxxx
> > Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
> > Subject: Re: Does SQL by chance currently support row look-behind and look-ahead?
> >
> > I googled for "join row with previous row sql" and got this hit -
> > https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fstackoverflow.com%2Fquestions%2F710212%2Fis-there-a-way-to-access-the-previous-row-value-in-a-select-statement&data=05%7C01%7C%7C541c27e1c63645b92ee108db210cbd6a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638140110828904079%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=urOZ46hxYW%2Bvlhwezd2pZcjtgPNEcItCoMfseJuY2pc%3D&reserved=0
> >
> > 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.ibm.com%2Fdocs%2Fen%2Fssw_ibm_i_73%2Fpdf%2Frbafzpdf.pdf&data=05%7C01%7C%7C541c27e1c63645b92ee108db210cbd6a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638140110829060298%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=MUiP3FkETT2pPe7uRLPTQkNbBr4nwlqJZBAGwsYTxDA%3D&reserved=0, 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.