Blogs / SAP / SAP BW/HANA / SAP BW/4HANA / Basic Operations and Windows Function in AMDP

Basic Operations and Windows Function in AMDP

Jul 15, 2020

SHARE

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

Basic Operations and Windows Function in AMDP

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.

Basic Operations and Windows Function in AMDP

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.

Basic Operations and Windows Function in AMDP

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.

Basic Operations and Windows Function in AMDP

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.

Basic Operations and Windows Function in AMDP
Basic Operations and Windows Function in AMDP

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.

Basic Operations and Windows Function in AMDP
Basic Operations and Windows Function in AMDP

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


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!