Day 10: Table Functions
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
🔹 1. What is CDS Table Function?
A CDS Table Function is a special type of CDS view that is powered by AMDP (ABAP Managed Database Procedure) in the background.
💡 Key Idea:
CDS Table Function allows you to use SQLScript (procedural logic, loops, conditions, dynamic SQL) to fetch and return data in scenarios where normal CDS views are not sufficient.
🔹 2. CDS Table Function vs CDS View vs AMDP
✅ Table Functions = Best of both worlds – used where you need logic + CDS.
🔹 3. Can we write SQLScript inside TF?
Yes. A CDS Table Function is only the interface, the actual logic is written in an AMDP method in SQLScript.
🔧 How it works:
You define the table function interface with define table function.
You implement the logic using SQLScript inside a class method with IF_AMDP_MARKER_HDB.
✅ YES: You can use joins, loops, conditional logic, CASE, IF, etc.
🔹 4. When to use Table Function over AMDP?
✅ Use Table Function when:
You want complex logic (loops, variables, cursor logic) but still want to expose it like a CDS View.
You want to join multiple tables with conditional logic.
You need to reuse it in VDM layers or RAP.
❌ Don't use Table Function when:
You just need standard SQL joins — use CDS instead.
You want to update/delete/insert data — use AMDP.
🔹 5. Create a Simple Table Function for Custom Reporting Logic
✅ Scenario: Return Top 5 customers based on sales
Step 1: Define the CDS Table Function
define table function ZTF_Top5_Customers
with parameters
p_country : abap.char(3)
returns
{
kunnr : kunnr,
name1 : ad_name1,
sales : abap.curr_17_2
}
implemented by method zcl_tf_customer=>get_top5.
Step 2: Implement in AMDP Class
CLASS zcl_tf_customer DEFINITION
PUBLIC
FINAL
CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_top5
FOR TABLE FUNCTION ZTF_Top5_Customers.
ENDCLASS.
CLASS zcl_tf_customer IMPLEMENTATION.
METHOD get_top5 BY DATABASE FUNCTION
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING kna1 vbak.
RETURN
SELECT kna1.kunnr,
kna1.name1,
SUM(vbak.netwr) as sales
FROM kna1
INNER JOIN vbak ON kna1.kunnr = vbak.kunnr
WHERE kna1.land1 = :p_country
GROUP BY kna1.kunnr, kna1.name1
ORDER BY sales DESC
LIMIT 5;
ENDMETHOD.
ENDCLASS.
🔹 6. Complex Real-Time Example – Sales Report with TF + CDS + AMDP
✅ Scenario: Generate a report of sales orders, showing:
Sales Order No
Customer Name
Country
Total Amount (Sum of all items)
Order Category (CASE-based)
Filterable by Country (as parameter)
Step-by-step Layers
a) Table Function ZTF_Sales_Report
define table function ZTF_Sales_Report
with parameters p_country : abap.char(3)
returns {
vbeln : vbak_vbeln,
name1 : ad_name1,
land1 : land1,
amount : abap.curr_17_2,
category : abap.char(10)
}
implemented by method zcl_tf_sales=>get_sales_data.
b) AMDP Implementation
METHOD get_sales_data BY DATABASE FUNCTION
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING vbak vbap kna1.
RETURN
SELECT v.vbeln,
k.name1,
k.land1,
SUM(p.netwr) AS amount,
CASE
WHEN SUM(p.netwr) > 10000 THEN 'HIGH'
WHEN SUM(p.netwr) > 5000 THEN 'MEDIUM'
ELSE 'LOW'
END AS category
FROM vbak AS v
INNER JOIN vbap AS p ON v.vbeln = p.vbeln
INNER JOIN kna1 AS k ON v.kunnr = k.kunnr
WHERE k.land1 = :p_country
GROUP BY v.vbeln, k.name1, k.land1;
ENDMETHOD.
c) CDS View Layer on Top of Table Function
@EndUserText.label: 'Sales Order Report'
@AccessControl.authorizationCheck: #CHECK
@OData.publish: true
define view entity ZI_SalesReport
as select from ZTF_Sales_Report( p_country: 'DE' )
{
key vbeln,
name1,
land1,
amount,
category
}
🔍 URI Testing in Browser (Optional)
Once OData is published:
/sap/opu/odata/sap/ZUI_SALESREPORT_CDS/$metadata
/sap/opu/odata/sap/ZUI_SALESREPORT_CDS/ZI_SalesReport?$filter=category eq 'HIGH'
Day 11: CDS + AMDP Real-Time Scenarios
CDS views used in Fiori List Reports
AMDP used in mass data reporting
TF used in dashboards
Practice:
Integrate CDS > TF > OData for real app simulation
Comments
Post a Comment