Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
JanBraendgaard
Active Participant
3,854

Introduction


New functions have been introduced in HANA for some years ago, but haven't received a lot of attention. The two functions we will cover here are LAG() and LEAD().

The functions provides access from the current row to the previous and next row in the dataset.

Function Details


Using the LAG() function it is possible to access fields from a previous row from the SELECT statement of the current row. The function must be used in conjunction with OVER() which provides a «window» the table content defined by the «PARTITION BY» clause.

The same are valid for the LEAD() function, which provides access to the next row.



Example


In this example we calculate the number of free seats on the previous and next flight out of Frankfurt.
SELECT 
sflight~carrid,
sflight~connid,
sflight~fldate,
sflight~seatsmax,
sflight~seatsocc,
( sflight~seatsmax - sflight~seatsocc ) AS seatsfree,
( LAG( sflight~seatsmax )
OVER( PARTITION BY sflight~carrid ORDER BY fldate )
- LAG( sflight~seatsocc )
OVER( PARTITION BY sflight~carrid ORDER BY fldate ) ) AS seatsfree_previous,
( LEAD( sflight~seatsmax )
OVER( PARTITION BY sflight~carrid ORDER BY fldate )
- LEAD( sflight~seatsocc )
OVER( PARTITION BY sflight~carrid ORDER BY fldate ) ) AS seatsfree_next
FROM spfli
INNER JOIN sflight ON spfli~carrid = sflight~carrid AND spfli~connid = sflight~connid
WHERE spfli~cityfrom = 'FRANKFURT'
ORDER BY sflight~carrid, sflight~fldate
INTO TABLE @DATA(lt_sflights).

 

This gives the following result in the internal table lt_sflights:



Conclusion


The real value will appear when we select just one row from the database, and are provided with results from three rows. We could select a flight and get information of the previous and next flight with just one access to the database.

 

References


LAG function: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/e7ef7cc478f14a408e1af27fc1...

LEAD function: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/5932eebb6208406590071eb65c...
5 Comments
Labels in this area