SAP SELECT OVER ABAP Statements Get Example source ABAP code based on a different SAP tableABAP Statement
• OVER ABAP_OSQL_WIN_EXP
• PARTITION BY ABAP_OSQL_WIN_EXP
• ORDER BY ABAP_OSQL_WIN_EXP
• ROWS BETWEEN ABAP_OSQL_WIN_EXP
• ( ) ABAP_OSQL_WIN_EXP
ABAP_SQLEXP - sql_win ABAP_SYNTAX ... win_func OVER( $[PARTITION BY sql_exp1, sql_exp2 ...$] col1 $[ASCENDING$|DESCENDING$], col2 $[ASCENDING$|DESCENDING$] ABAP Addition ... ORDER BY col1 $[ASCENDING$|DESCENDING$], col2 $[ASCENDING$|DESCENDING$], ... ... ROWS BETWEEN ... What does it do? Window expression in AB_SQL . A window expression uses a window function to determine an individual value from the rows of a window of the result set of a query. A window expression can be specified as an SQL expression or as an argument of any SQL expressions for defining a column col_spec of the result set in the SELECT list of a query. Aggregate expressions can also be nested if there is interest in both the aggregation and windowing in the same query. In each row of the result set, the result of the calculation is placed in the column defined in this way. win_func followed by the addition OVER( ... ) in whose parentheses the window on the result set is defined for whose rows the window function is evaluated: PARTITION BY sql_exp1, sql_exp2 ... The optional addition PARTITION BY defines the windows using a comma-separated list of SQL expressions sql_exp1, sql_exp2 , ... A window is constructed by the rows of the result set for which all SQL expressions have the same result. All SQL expressions except for aggregate expressions and window expressions can be used. The window function is calculated for the rows of the respective current window. If PARTITION BY is not specified, a single window is constructed that comprises all rows of the result set. ORDER BY The optional addition ORDER BY introduces both an order and a frame within the current window, which further restricts the rows for which the window function is calculated. It is also a prerequisite for certain ranking functions. SELECT list of a SELECT statement with GROUP BY clause, the windows are defined in the merged result list and aggregate expressions can be used as arguments of the window functions. Each column that is specified in any position in the window expression must also be specified in the GROUP BY clause. Latest notes: A window expression works in a similar way to an aggregate expression, whereby the addition PARTITION BY assumes the role of the GROUP BY clause. Unlike aggregate expressions, however, there is no aggregation of the rows defined using PARTITION BY. Instead, these are retained and are all assigned the value calculated using the window expression. The window functions of a window expression work with the rows of a virtual table, which is defined by the specifications after OVER. Using the addition ORDER BY of the SELECT statement has no effect on the result of a window expression. However, the rows of the result set can themselves be sorted according to the results of window expressions by using their alias name. A window expression can only be specified in the result set in the SELECT list of a query. It cannot be used like an aggregate expression in the GROUP BY or HAVING clause. By using window expressions in other SQL expressions, calculations can be performed, which combine values of the current row with the results of window expressions, for example, the percentage of a column in the current window, or the distance to the minimum or maximum value of the current window. Window expressions are not supported by all databases. In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports the use of window expressions. This requires the constant WINDOWING of this class to be passed to the method in an internal table. ABAP_EXAMPLES_ABEXA Window Expressions Window Expressions without Partition ABAP Addition What does it do? ORDER BY, which can be specified independently of PARTITION BY, defines an order in the current window and an evaluation framework for the window function. ORDER BY is followed by a comma-separated list of columns of the data sources of the current query. These columns are used to sort the rows of the window. A column can only be specified directly using the column name col1, col2 ... Alias names defined using AS cannot be specified. The additions ASCENDING and DESCENDING determine whether the rows are sorted in ascending or descending order by the column in question. The default is ASCENDING . ORDER BY causes the following: The rows of the window are processed by the window function in the order defined by the sort order. The order of the processing of rows that appear more than once regarding the sort criterion is not defined. If the addition ORDER BY is not specified, this applies to all rows of the window. The rows processed by the window function are additionally restricted by a frame. Only the rows of the window that are in front of the current row in the sorting, or that have the same values in the columns of the sort criterion are respected. aggregate functions specified as a window function, the processing sequence primarily affects the ranking functions . The addition ORDER BY must be specified for the ranking functions RANK and DENSE_RANK. Latest notes: ORDER BY following OVER is totally independent of the addition ORDER BY clause of the SELECT statement. ABAP_EXAMPLE_ABEXA Window Expressions with Sort ABAP Addition What does it do? OVER( ... ORDER BY ... ) clause. It allows the definition of a subset of rows inside a window, which is also referred to as a frame. Frames are determined with respect to the current row, which enables the frame to move within a window. ${UNBOUNDED PRECEDING$} or ${UNBOUNDED FOLLOWING$} UNBOUNDED PRECEDING as starting frame boundary specifies that the frame starts at the first row of the window. UNBOUNDED FOLLOWING as ending frame boundary specifies that the frame ends at the last row of the partition. CURRENT ROW can be used as both starting and ending frame boundary. It specifies that the window starts or ends at the current row, including the current row in the frame. ${n PRECEDING$} or ${n FOLLOWING$} n PRECEDING can be used as both starting and ending frame boundary. It specifies that the frame starts or ends n rows above the current row. n FOLLOWING can be used as both starting and ending frame boundary. It specifies that the frame starts or ends n rows beneath the current row. n must be 0, a positive integer literal, or a host expression that can be resolved into a constant of type b, s, i, or int8. ORDER BY is mandatory. Latest notes: The ending frame boundary cannot be smaller than the starting frame boundary. If no window frame is used, the default window frame is BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is to say that the window function computes all rows up to the current row. As a result, the function returns cumulative values. ABAP_EXAMPLE_VX5 window functions are applied to frames inside a window. count sorts the rows by column ID and counts the number of rows from the first row of the window to the current row. The result is the same as that returned by the ROW_NUMBER( ) function. count_reverse sorts the rows by column ID and counts the number of rows from the current row to the last row of the window. The result is reverse numbering. average sorts the rows by column ID and calculates the rolling averages of a subset of rows from column COL1. The subset consists of the current row plus one preceding and one following row. With this function, it is possible, for example, to calculate the <(>3-day-average<)> temperature for every day from a list of temperature data. accumulate sorts the rows by column ID and accumulates the values from the first row of the window up to the current row, thus computing the running total of column COL1. ABAP_EXAMPLE_ABEXA Window Frame Specification Return to menu
