Day 9: AMDP Advanced Concepts

 

---

Day 9: AMDP Advanced Concepts

• Insert, Update, Delete in AMDP

• Error handling and exception class

• Performance tuning tips

Hands-on:

• Create AMDP for aggregation and update logic

---

🔹 1. Operations: Insert, Update, Delete in AMDP

🧠 Concept:

AMDP methods allow not just SELECTs, but also DML operations: INSERT, UPDATE, DELETE, UPSERT. These statements are written in SQLScript inside the AMDP method.

💡 Must be declared as MODIFYING methods.

---

✅ Example – ZCL_AMDP_DML_EXAMPLE (Insert, Update, Delete)

CLASS ZCL_AMDP_DML_EXAMPLE DEFINITION

  PUBLIC

  CREATE PUBLIC.

  PUBLIC SECTION.

    INTERFACES: IF_AMDP_MARKER_HDB.

    CLASS-METHODS:

      insert_sales_order_data

        IMPORTING VALUE(it_data) TYPE STANDARD TABLE OF zsales_order,

      update_sales_order_data

        IMPORTING VALUE(iv_status) TYPE zstatus

                  VALUE(iv_vbeln) TYPE vbak-vbeln,

      delete_old_orders

        IMPORTING VALUE(iv_cutoff_date) TYPE vbak-erdat.

ENDCLASS.

CLASS ZCL_AMDP_DML_EXAMPLE IMPLEMENTATION.

  METHOD insert_sales_order_data BY DATABASE PROCEDURE

    FOR HDB

    LANGUAGE SQLSCRIPT.

    INSERT INTO zsales_order

      SELECT * FROM :it_data;

  ENDMETHOD.

  METHOD update_sales_order_data BY DATABASE PROCEDURE

    FOR HDB

    LANGUAGE SQLSCRIPT.

    UPDATE zsales_order

    SET status = :iv_status

    WHERE vbeln = :iv_vbeln;

  ENDMETHOD.

  METHOD delete_old_orders BY DATABASE PROCEDURE

    FOR HDB

    LANGUAGE SQLSCRIPT.

    DELETE FROM zsales_order

    WHERE erdat < :iv_cutoff_date;

  ENDMETHOD.

ENDCLASS.

You can test this from a simple ABAP report or unit test.

---

🔹 2. Error Handling and Exception Class in AMDP

SQLScript doesn’t use TRY...CATCH like ABAP, but you can simulate error checks using:

• SIGNAL statement for raising custom exceptions

• EXIT HANDLER for handling exceptions (like divide by 0)

• In ABAP, wrap the call in TRY...CATCH to catch system/AMDP exceptions.
  
  

✅ Example:

IF :iv_vbeln IS NULL THEN

   SIGNAL SQL_ERROR 'Missing VBELN!';

END IF;

In ABAP:

TRY.

  zcl_amdp_dml_example=>update_sales_order_data( ... ).

CATCH cx_amdp_execution_failed INTO DATA(lx_amdp).

  MESSAGE lx_amdp->get_text( ) TYPE 'E'.

ENDTRY.

---

🔹 2.1  Lets  see Error Handling and Exception Class in Detail

👉 In AMDP methods, you CANNOT directly raise classic ABAP exceptions (RAISE EXCEPTION TYPE) inside the SQLScript body.

Because:

• SQLScript (HANA language inside AMDP) doesn't support ABAP-style exception handling.
  

• SQLScript and ABAP have different error handling models.
  
  

---

🔥 So how is Error Handling done in AMDP?

Situation	What to do?
Technical SQL Errors (DB issues)	SAP automatically raises a standard CX_AMDP_EXECUTION_FAILED exception.
Logical validation errors (business rule failure)	You must return special error codes or messages manually (via output parameters).
Controlled error throwing	Raise errors using SIGNAL keyword inside SQLScript.

---

1️⃣ Automatic Error Handling

If anything fails inside AMDP (wrong query, wrong data, missing table),
SAP HANA throws a system error → ABAP Runtime raises:

✅ Exception Class:

CX_AMDP_EXECUTION_FAILED

You can catch this in ABAP like:

TRY.

    zcl_sales_amdp=>get_sales_data(

      EXPORTING

        iv_sales_org = '1000'

      IMPORTING

        et_sales_data = lt_sales_data

    ).

  CATCH cx_amdp_execution_failed INTO DATA(lx_amdp).

    MESSAGE lx_amdp->get_text( ) TYPE 'E'.

ENDTRY.

✅ You don't have to raise manually — it comes from the runtime.

---

2️⃣ Manual Error Handling Inside AMDP

SQLScript inside AMDP allows controlled error throwing using SIGNAL:

✅ Example inside AMDP:

IF :iv_sales_org IS NULL THEN

  SIGNAL SQL_ERROR 'Invalid Sales Org: cannot be initial';

END IF;

➡️ This will throw an error back to ABAP caller, which again becomes CX_AMDP_EXECUTION_FAILED.

You can read the error message using:

MESSAGE lx_amdp->get_text( ) TYPE 'E'.

---

3️⃣ Design Best Practice: Returning Structured Errors

Rather than crashing AMDP, you can design your AMDP method to return a status table:

Example:

et_return = SELECT

              'E' AS type,

              'Invalid Sales Org' AS message

            FROM dummy_table

            WHERE :iv_sales_org IS NULL;

✅ This way, no crash, but you pass error info cleanly back to ABAP caller.

---

🧠 Important Facts for Interviews

Topic	Statement
Can you RAISE EXCEPTION TYPE inside AMDP SQLScript?	❌ No
How are errors raised automatically?	Via CX_AMDP_EXECUTION_FAILED
Can you control errors inside AMDP?	✅ Yes, using SIGNAL
Should business validations be inside AMDP?	❌ Prefer to validate in ABAP unless unavoidable.

---

📑 Quick Summary

Case	Handling
DB error (select/insert failure)	SAP auto-raises CX_AMDP_EXECUTION_FAILED
Business validation error	Use SIGNAL inside SQLScript or return error structure
Catching error in ABAP	Use TRY...CATCH cx_amdp_execution_failed

---

✅ Golden Rule:

In AMDP, you don't RAISE ABAP exceptions manually — HANA errors become CX_AMDP_EXECUTION_FAILED. For business validations, use SIGNAL or return structured error results.

---

🔹 3. SAP-Recommended Performance Tuning Tips for AMDP

Tip	Description
✅ Push logic to DB	Avoid unnecessary loops in ABAP, use SQLScript in AMDP
✅ Use proper indexes	Especially on JOINed or filtered fields
✅ Use EXPLAIN PLAN	To analyze performance
✅ Avoid nested SELECTs	Use JOINs and aggregations
✅ Minimize data transfer	Filter at DB level using parameters
✅ Use scalar variables	Instead of intermediate tables when possible
✅ Monitor with ST05, ST12, ADBC, HANA Studio	Check expensive operations

---

🔹 4. Complex AMDP – Aggregation & Update Logic

✅ Use Case:

We want to aggregate total sales per customer and update a custom table with that aggregated data.

---

🗂️ Project Overview:

• Source Table: VBRK (Billing Header) and VBRP (Billing Items)

• Target Custom Table: ZCUST_SALES_SUM
  Fields:

• KUNNR (Customer)

• TOTAL_NETWR (Total Net Value)

• UPDATED_ON (Timestamp)
  
  

---

1️⃣ Step: Create Custom Table

@EndUserText.label: 'Customer-wise Sales Aggregation Table'

@AbapCatalog.tableCategory: #TRANSPARENT

define table ZCUST_SALES_SUM {

  key kunnr        : kunnr;

      total_netwr  : netwr;

      updated_on   : timestampl;

}

This table stores total net value of billing per customer and is updated periodically.

---

2️⃣ Step: Define AMDP Class for Aggregation & Update

➤ Class Declaration: ZCL_AGGREGATE_SALES

CLASS ZCL_AGGREGATE_SALES DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC.

  PUBLIC SECTION.

    INTERFACES: IF_AMDP_MARKER_HDB.

    CLASS-METHODS aggregate_and_update_sales

      IMPORTING VALUE(iv_budat_from) TYPE budat

                VALUE(iv_budat_to)   TYPE budat.

ENDCLASS.

---

➤ Class Implementation with SQLScript

CLASS ZCL_AGGREGATE_SALES IMPLEMENTATION.

  METHOD aggregate_and_update_sales

    BY DATABASE PROCEDURE

    FOR HDB

    LANGUAGE SQLSCRIPT

    OPTIONS READ-ONLY.

    -- Step 1: Aggregate net value from VBRK and VBRP

    sales_data = 

      SELECT

        a.kunag     AS kunnr,

        SUM(b.netwr) AS total_netwr

      FROM vbrk AS a

      INNER JOIN vbrp AS b

      ON a.vbeln = b.vbeln

      WHERE a.fkdat BETWEEN :iv_budat_from AND :iv_budat_to

      GROUP BY a.kunag;

    -- Step 2: Merge results into custom table

    FOR i IN 1..CARDINALITY(:sales_data) DO

      upsert ZCUST_SALES_SUM

        VALUES (:sales_data[i].kunnr,

                :sales_data[i].total_netwr,

                CURRENT_TIMESTAMP)

        WHERE kunnr = :sales_data[i].kunnr;

    END FOR;

  ENDMETHOD.

ENDCLASS.

---

🧪 Explanation:

Section	Description
SELECT ...	Joins billing header and item tables to sum up net value per customer (KUNNR) for given date range
sales_data	Local SQLScript internal table to hold aggregation results
UPSERT	Inserts or updates the data into custom table (ZCUST_SALES_SUM)
CURRENT_TIMESTAMP	Records the last update time
FOR ... IN	Iterates over each record in sales_data

---

3️⃣ Step: Call AMDP from ABAP (optional)

CALL METHOD ZCL_AGGREGATE_SALES=>aggregate_and_update_sales

  EXPORTING

    iv_budat_from = '20240101'

    iv_budat_to   = '20241231'.

You can schedule this logic using a report program or background job.

---

🛠️ Testing Strategy:

Step	Task
1	Populate some dummy billing data in VBRK/VBRP
2	Execute the AMDP method manually or via a wrapper report
3	Check contents of ZCUST_SALES_SUM using SE16N
4	Run again with different iv_budat_from to test updates

---

🔒 Security & Optimization Notes:

• Make sure the ZCUST_SALES_SUM table has proper indexes on KUNNR.

• Consider locking or transactional control if used in real-time scenario.

You can parameterize the logic further by sales organization or country.

---

✅ What is UPSERT in SQLScript (SAP HANA)?

UPSERT is a shortcut for “update or insert” — a merge operation.

• If the record exists (based on primary key), it updates the entry.

• If the record doesn't exist, it inserts the new entry.

Syntax (HANA SQLScript):

UPSERT <table_name>

  VALUES (<value_list>)

  [WHERE <primary_key_column> = <value>];

✅ Is it supported in AMDP?

Yes, absolutely — but with these conditions:

Rule	Detail
🟢 Supported?	Yes, UPSERT is supported in HANA SQLScript inside AMDP
🔒 Target Table	Must be a transparent table, not a CDS view
🔑 Key Field	Must have primary key defined — used in WHERE clause
📚 SAP Reference	SAP HANA SQLScript Reference Guide and SAP Blogs - AMDP

---

Day 10: Table Functions

• CDS Table Functions vs Normal CDS

• SQL Script logic inside TF

• When to use Table Function over AMDP

Hands-on:

• Create a TF for custom reporting logic

---