[[{“value”:”

If like me you don’t know well the ABAP SQL window functions (partitions with OVER, window functions) and have difficulties to quickly choose between them, here’s a compilation of the ABAP Keyword Documentation + ABAP results. It concerns ABAP 7.58.

• AVG (ask me if you need it)
• CORR (ask)
• CORR_SPEARMAN (ask)
• COUNT
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• MAX
• MEDIAN (ask)
• MIN
• NTILE
• RANK
• ROW_NUMBER
• STDDEV (ask)
• VAR (ask)

FIRST_VALUE, LAST_VALUE

Content of table DEMO_UPDATE:

ABAP SQL:

SELECT
id,
col1,
col2,
col3,
FIRST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS first_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS last_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
AS last_value_correct
FROM demo_update
INTO TABLE @FINAL(result).

Result (copied from ABAP Keyword Documentation):

LAG, LEAD

Content of table DEMO_EXPRESSIONS:

ABAP SQL:

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 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_ascending) ##no_text.

Result:

ABAP SQL:

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 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_descending) ##no_text.

Result:

NTILE

Content of table DEMO_EMPLOYEES:

ABAP SQL:

SELECT name,
salary,
NTILE( 5 ) OVER( ORDER BY salary ) AS ntile
FROM demo_employees
INTO TABLE @FINAL(result).

Result:

count, dense_rank, max, min, rank, row_number, sum

Content of table DEMO_EXPRESSIONS:

ABAP SQL:

SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1 ) AS rnum,
‘-‘ AS rank,
‘-‘ AS schlank,
MAX( num1 ) OVER( PARTITION BY char1 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_no_order).

Result:

ABAP SQL:

SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_order_ascending).

Result:

ABAP SQL:

SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1 DESCENDING, char2 DESCENDING
INTO TABLE @FINAL(windowed_order_descending).

Result:

“}]] 

  Read More Technology Blog Posts by Members articles 

#abap