ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_win →  ABAP SQL - Examples of Window Expressions 

SQL Expressions, Window Functions LEAD and LAG

This example demonstrates window expressions with the window functions LEAD and LAG.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    SELECT char1, char2,
           num1,
           ROW_NUMBER( )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS rnum,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 1, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lead1,
           LAG( CAST( num1 AS CHAR( 11 ) ), 1, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lag1,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 2, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lead2,
           LAG( CAST( num1 AS CHAR( 11 ) ), 2, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lag2,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 10, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lead10,
           LAG( CAST( num1 AS CHAR( 11 ) ), 10, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 )
               AS lag10
           FROM demo_expressions
           ORDER BY char1, char2
           INTO TABLE @DATA(windowed_order_ascending).
    out->write( windowed_order_ascending ).

    SELECT char1, char2,
           num1,
           ROW_NUMBER( )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS rnum,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 1, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lead1,
           LAG( CAST( num1 AS CHAR( 11 ) ), 1, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lag1,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 2, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lead2,
           LAG( CAST( num1 AS CHAR( 11 ) ), 2, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lag2,
           LEAD( CAST( num1 AS CHAR( 11 ) ), 10, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lead10,
           LAG( CAST( num1 AS CHAR( 11 ) ), 10, 'Nope' )
             OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
               AS lag10
           FROM demo_expressions
           ORDER BY char1, char2
           INTO TABLE @DATA(windowed_order_descending).
    out->write( windowed_order_descending ).

    out->display( ).

Description

The example shows results of the window functions LEAD and LAG to which all three possible arguments are passed. Like in the executable example, the windows are determined for window expressions with sorts.

• The first argument determines the result as a character-like representation of the column NUM1 of the row determined by the second argument.

• The second argument determines the evaluated rows using differently sized distances.

• The third argument is a literal returned instead of the null value if the evaluated row is not in the current window.