ABAP Managed Database Procedures are a new feature in AS ABAP allowing developers to write database procedures. As you can imagine calculations executed while retrieving the data from the database can significantly reduce the runtime of an ABAP program. Especially when the calculations can be parallelized. This is clear from the way we define the AMDP Method. We need to let the Method know the database and language. For HANA, the database is HDB and language is SQLScript. This Blog deals with how the basic operations and windows functions can be handled in SAP BW using AMDP Script. We will see some basic operations and Windows functions in End routine.

SELECT

CASE, CONCATENATE, LPAD and Arithmetic formula

In the same select query in the Endroutine, we can have the SQL commands as per the sequence of the fields.

LOOKUP TABLE USAGE

In the Method declaration, we must include all the lookup Master tables/ADSO separated by space after USING keyword.

TABLE DECLARATION

We can create a local table as below “T_LOOKUP” is created and later is being looked up.

NESTED SELECT

Nested select is used in some cases where the result of the subquery is pushed to the above query. The example is as below. As /BIC/ZASCHSPEN is after the field /BIC/ZASCHPROF in the structure, the case statement should be used in the upper query. Because it cannot be reached in the same query.

Windows Functions

• RANK, ROW_NUMBER, DENSE_RANK
• RANK () – Return rank within a partition, starting from 1
• ROW_NUMBER() – Returns a unique row number within a partition
• DENSE_RANK() – Returns ranking values without gaps.

The Rank is based on the order by, Partition column defined in the query.

Here the Rank is based on the value /BIC/ZASCHREVE by descending.

Lead & Lag

Lead – Returns the value of the <offset>rows after the current row.
Lag – Returns the value of the <offset>rows before the current row.

Know more about Visual BI Solutions SAP BW Services offerings here.