Working with Hierarchies in ABAP SQL

This blog summarizes the possibilities, ABAP SQL offers together with ABAP CDS for working with hierarchical data that are stored in database tables. Hierarchical data in database tables means, that lines of one or more database tables are connected by parent-child relationships. There are many use cases, where hierarchical data play a role and where accessing information about the hierarchical relationship is important. For example, a common task can be to find out the descendants or ancestors of a given hierarchy node or to aggregate values of subtrees.

Overview

In former times you had to load the hierarchical data from the database into internal tables and program it all by yourself (if you did not find an appropriate API). In between, meshes offered some features for working with hierarchies, as shown in this example, but have not found wide distribution.

New Window Functions in ABAP SQL

Function that is specified in front of the definition of a window in an ABAP SQL window expression and evaluates the rows of the window, is known as window function. Such a function performs analytic operations over a set of input table rows that are somehow related to the current row and help us to solve complex query challenges in easy ways. Window functions are aimed for writing a single query which shows grouped information AND individual information in a query. You might say, it is also possible to use the GROUP BY clause to show grouped information. But the GROUP BY query has its limitation, in that it won’t show you individual data as well.

The addition OVER designates a function as a window function. If you’d like to narrow the window from the entire dataset to individual groups within the dataset, you can use PARTITION BY to do so. You can use the ORDER BY clause as well in defining a window function. It simply orders by the designated column(s) the same way the ORDER BY clause would, except that it treats every partition as separate. The ORDER and PARTITION define what is referred to as the “window”, the ordered subset of data over which calculations are made. Detailed explanation on the windowing syntax and related additions can be found in this blog about Window Expressions in ABAP SQL.

Window Expressions in ABAP SQL

In ABAP 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. The term window describes the set of rows on which the function operates. In other words, a window function uses values from the rows in a window to calculate the returned values.

A window expression can be specified as an SQL expression or as an argument of any SQL expressions, except for aggregate and defining a column of the result set in the SELECT list of a query. In each row of the result set, the result of the calculation is placed in the column defined in this way.

Smartforms OOPS (object oriented)

I have not had that much opportunity to work with SmartForms till very recently. I had already switched to Eclipse & ADT as my primary programming tool and thought I could get away this, not having to go back to SAPGui based development – and then I encounter SmartForms! I can’t even use normal day-to-day ABAP Editor tools, can’t use normal breakpoints, can’t do a where-used, several drill-throughs don’t work – what did I just get myself into!

After the first one that I volunteered making changes to, we realized I had to do one from scratch. Here is an opportunity – I moved all the code out to a class and called the class from within the SmartForm. This way I have all the tools that SAP & ABAP provide to work with code, I can use the ADT environment, test the data output independent of the SmartForm, use editor-based breakpoints, etc, etc…

General properties of ABAP Classes / Interfaces

Every day you create or change ABAP class in class builder. When you activate your change, have you noticed a series of objects with several "=" in the middle part of each?

Building an SAP Query with ABAP Code

ABAP code is used with SAP query tool to enhance the query output.

You can write down the code under the Extras tab for the Infoset in the SQ02 Tcode.

New Open SQL Enhancement in 740

The following open SQL statement looks a little werid, however it could really works in 740.