CDS Table Functions Part 2: Building a Table Function with an AMDP Implementation

📌 KEY POINTS
✅ CDS side: define table function … with parameters … returns { … } implemented by method class=>method.
✅ ABAP side: class with if_amdp_marker_hdb and a static method FOR TABLE FUNCTION.
✅ Implementation: BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING <tables>.
⚠️ The SELECT list must match the returns structure exactly — names, order and types.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 1 justified a table function for "age rank per supplier". Now we build ZTF_PurchReqAgingRank.

━━━━━━━━━━━━━━━━━━━━
▶ STEP 1 — THE CDS DEFINITION

• "define table function ZTF_PurchReqAgingRank".
• Parameters: p_keydate (the date for the age calculation).
• "returns { … }": the output structure. For client-dependent data, the first element is the client field (Part 3).
• "implemented by method zcl_tf_purchreq_aging=>get_aging_rank".

Create it in ADT via New → Data Definition → template "Define Table Function with Parameters".

━━━━━━━━━━━━━━━━━━━━
▶ STEP 2 — THE AMDP CLASS

• PUBLIC SECTION: INTERFACES if_amdp_marker_hdb — marks the class as AMDP for HANA.
• CLASS-METHODS get_aging_rank FOR TABLE FUNCTION ztf_purchreqagingrank. — the signature comes from the CDS definition; you do not declare parameters yourself.

━━━━━━━━━━━━━━━━━━━━
▶ STEP 3 — THE SQLSCRIPT IMPLEMENTATION

• METHOD … BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING zpr_head.
• USING lists every database object you read (tables, views, other AMDP functions).
• RETURN SELECT … ; — one result set matching "returns".
• Parameters are available by name (p_keydate); in SQLScript write :p_keydate when used as a variable.

━━━━━━━━━━━━━━━━━━━━
▶ STEP 4 — ACTIVATE AND TEST

• Activate the CDS definition and the class (together if needed).
• Open Data Preview on the table function; enter the parameter.
• Consume it from ABAP SQL: SELECT FROM ztf_purchreqagingrank( p_keydate = @today ) …

━━━━━━━━━━━━━━━━━━━━
▶ STEP 5 — WRAP IT FOR CONSUMERS
Expose the table function through a CDS view entity on top (names, associations, annotations, access control). Consumers then use the view, not the function directly.

💡 TIP: keep the SQLScript as a single declarative SELECT whenever possible; imperative loops and cursors are slow and hard to optimize.

✅ CHECKPOINT: you can create, activate and consume a table function.

A developer's first table function failed to activate with "result structure does not match". The SQLScript returned request_id before request_uuid and used a DECIMAL for DaysOpen while the CDS returns structure declared INT4.
After aligning the column order, aliases and types exactly with the returns structure, activation worked. The team added a comment block at the top of every table function method listing the returns fields in order, which prevented the same issue in later objects.

• SELECT list not matching the returns structure (order, names, types).
• Forgetting tables in the USING clause.
• Missing if_amdp_marker_hdb.
• Forgetting the client filter for client-dependent data.
• Imperative loops instead of set-based SQL.
• Consumers reading the function directly instead of a wrapping view.

• Start from the ADT template.
• Mirror the returns structure exactly in the RETURN SELECT.
• Keep SQLScript declarative and short.
• Handle client explicitly.
• Wrap the function in a view entity for consumers.

Questions: "How do you create a CDS table function?", "What does FOR TABLE FUNCTION mean?", "What must match between CDS and SQLScript?"
Strong answer: CDS "define table function" with parameters, returns structure and "implemented by method"; an AMDP class with if_amdp_marker_hdb and a static method FOR TABLE FUNCTION (signature derived from CDS); implementation BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING …, returning a SELECT whose columns match the returns structure; handle client; wrap in a view entity.