CDS Table Functions Part 1: When You Really Need a Table Function

📌 KEY POINTS
✅ A table function = CDS entity whose data is computed by an AMDP method in SQLScript.
✅ Try a normal CDS view entity first — CASE, casts, aggregation, unions, associations cover most needs.
✅ Use a table function only for logic CDS cannot express efficiently (complex ranking, multi-step SQLScript).
⚠️ Table functions are database-specific (HANA), harder to test and get fewer optimizations — they have a cost.

━━━━━━━━━━━━━━━━━━━━
▶ WHAT THIS SERIES COVERS

• Part 1 — when you really need a table function (this lesson)
• Part 2 — building one with an AMDP implementation
• Part 3 — parameters, client handling and session context
• Part 4 — performance with HANA plan analysis
• Part 5 — testing, clean core and alternatives

Running example: purchase request aging with ranking per supplier.

━━━━━━━━━━━━━━━━━━━━
▶ WHAT A TABLE FUNCTION IS
It has two parts:

• A CDS definition ("define table function") with parameters and a "returns" structure — consumers see it like any CDS entity.
• An AMDP class method ("FOR TABLE FUNCTION") written in SQLScript that returns the result table.

Consumers — other CDS views, ABAP SQL, RAP queries, analytics — do not see the difference.

━━━━━━━━━━━━━━━━━━━━
▶ WHAT CDS VIEW ENTITIES ALREADY DO
Before writing SQLScript, check whether a view entity can do it:

• Calculations: CASE, CAST, arithmetic, string functions, date functions (DATS_DAYS_BETWEEN).
• Aggregations: SUM, COUNT, MIN, MAX with GROUP BY and HAVING.
• Combinations: joins, associations, UNION.
• Parameters with session defaults.
• Currency/unit conversion functions.

Example: "days open" per request is just DATS_DAYS_BETWEEN(created_on, $session.system_date) — no table function needed.

━━━━━━━━━━━━━━━━━━━━
▶ WHEN A TABLE FUNCTION IS JUSTIFIED

• Ranking and window logic, e.g. "the three oldest open requests per supplier" (ROW_NUMBER() OVER (PARTITION BY …)) where your release does not support it in CDS.
• Multi-step logic with intermediate tables that would need several stacked views and still be inefficient.
• Special HANA functions not available in CDS.
• Performance-critical logic proven (by measurement) to be faster in SQLScript.

━━━━━━━━━━━━━━━━━━━━
▶ THE COSTS

• HANA-specific code; database knowledge needed for maintenance.
• Optimizer cannot push consumer filters into the function body as freely as into views — selective inputs should be parameters (Part 4).
• Client handling and authorization need care (Parts 3 and 5).
• In ABAP Cloud, only own objects and objects released for AMDP use (C4) may be accessed.

━━━━━━━━━━━━━━━━━━━━
▶ DECISION CHECKLIST

1. Can a view entity (or a stack of two) express it? → use CDS.
2. Is the logic ranking/iterative/multi-step? → consider a table function.
3. Is it performance-driven? → measure both versions first.
4. Document why CDS was not enough.

✅ CHECKPOINT: you can justify (or reject) a table function. Part 2 builds one.

A developer wrote a table function for "days open per purchase request" because "SQLScript is faster on HANA". A reviewer showed the same result as a five-line CDS view entity using DATS_DAYS_BETWEEN, which was easier to read, testable with standard tools, and — as ST05 showed — equally fast.
The table function was removed. Later, when the business asked for the three oldest open requests per supplier, the team did use a table function with ROW_NUMBER, documented why, and kept it small and isolated.

• Writing table functions for logic a view entity can express.
• Choosing SQLScript for "performance" without measuring.
• Forgetting client handling and authorization.
• Not documenting why a table function was needed.
• Using table functions as the default modeling tool.

• CDS view entities first; table functions as an exception.
• Use table functions for ranking, window and multi-step logic.
• Measure before and after when performance is the reason.
• Keep table functions small and well documented.
• Check ABAP Cloud rules (C4) for accessed objects.

Questions: "What is a CDS table function?", "When would you use one instead of a CDS view?"
Strong answer: a CDS entity whose result is computed by an AMDP method (FOR TABLE FUNCTION) in SQLScript; consumers use it like any CDS entity. Use it only when view entities cannot express the logic efficiently — ranking/window functions, multi-step SQLScript, special HANA functions — and justify performance claims by measurement. Mention costs: HANA dependency, filter pushdown limits, client handling, testing effort.