Quite often you can solve such problems without LAG() and LEAD() just by
joining the table with itself.
The following example will join a table with itself on Custumer No and Year
joined with the previous year:
Select a.*, b.*
From yourFile a full join yourFile b on a.CustNo = b.CustNo and a.Year =
b.Year - 1
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Roger
Harman
Sent: Friday, 10 March 2023 03:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Does SQL by chance currently support row look-behind and
look-ahead?
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%2Fstackoverfl
ow.com%2Fquestions%2F710212%2Fis-there-a-way-to-access-the-previous-row-valu
e-in-a-select-statement&data=05%7C01%7C%7C763aff32a4ce40a7d95208db21087d16%7
C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638140092558361233%7CUnknown%7CT
WFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
D%7C3000%7C%7C%7C&sdata=SgXNLM%2BL0dHelv8D6aO%2FhosUZn4ilcvvu4dHpAbhVPI%3D&r
eserved=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%7C763aff32a
4ce40a7d95208db21087d16%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C6381400
92558361233%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJB
TiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=yjgcdMuCols0tcQHpA8jo%2B8s5
u8CBtJgHYMxi1%2BaEzM%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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midra
nge.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7C%7C763aff32a4ce40a7
d95208db21087d16%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C63814009255836
1233%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1
haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=pACuurs%2Bqy8Zdep2DIHi%2FvI3RsgKiu
2ESnik2moeEhc%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.mid
range.com%2Fmidrange-l&data=05%7C01%7C%7C763aff32a4ce40a7d95208db21087d16%7C
84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638140092558361233%7CUnknown%7CTW
FpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D
%7C3000%7C%7C%7C&sdata=P3EXljWlexf06bUCyfgpunAVFCfZ1qWSGzUE4D53mUQ%3D&reserv
ed=0.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
