RAP / CDS / ODataadvanced

CDS Table Functions Part 3: Parameters, Client Handling and Session Context

Key takeaway

Handle runtime inputs in table functions correctly: parameters with system defaults, client handling with @ClientHandling and SESSION CONTEXT, language and date from the session, and passing parameters through wrapping views.

Explanation

📌 KEY POINTS
✅ Selective inputs (key date, company code) should be parameters — they reach the SQLScript directly.
✅ Client-dependent functions: client as first return field + filter on SESSION_CONTEXT('CDS_CLIENT').
✅ Defaults from the session via @Environment.systemField (#SYSTEM_DATE, #SYSTEM_LANGUAGE, #CLIENT).
⚠️ Forgetting the client filter returns data from ALL clients.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 2 built a table function. Real functions need correct inputs: dates, language, organizational units — and above all the right client.

━━━━━━━━━━━━━━━━━━━━
▶ 1. PARAMETERS

Declared in the CDS definition: with parameters p_keydate : abap.dats, p_bukrs : bukrs.
Available in SQLScript as :p_keydate.
Consumers must pass them: FROM ztf_…( p_keydate = @date ).
Wrapping views pass them on: as select from ZTF_…( p_keydate: $parameters.p_keydate ).

━━━━━━━━━━━━━━━━━━━━
▶ 2. SESSION DEFAULTS
Annotate parameters so they are filled automatically if the consumer (e.g. an OData service or analytics) does not supply them:

@Environment.systemField: #SYSTEM_DATE — today's date.
@Environment.systemField: #SYSTEM_LANGUAGE — logon language (for texts).
@Environment.systemField: #CLIENT — the client.
@Environment.systemField: #USER — the user.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CLIENT HANDLING
In ABAP SQL, the client is handled implicitly. In SQLScript it is not — you must do it.

Declare the function client-dependent: @ClientHandling.type: #CLIENT_DEPENDENT; the first element of "returns" is the client field.
@ClientHandling.algorithm: #SESSION_VARIABLE — the ABAP runtime sets the session variable CDS_CLIENT; your SQLScript filters on it: WHERE client = SESSION_CONTEXT('CDS_CLIENT') (on every client-dependent table you read, including joins).
Alternatively pass the client as a parameter annotated with #CLIENT and filter on it.

⚠️ WATCH OUT: without the filter, the function returns rows of every client in the database — a data leak and a source of duplicates.

━━━━━━━━━━━━━━━━━━━━
▶ 4. LANGUAGE
For texts, filter on a language parameter (#SYSTEM_LANGUAGE) or SESSION_CONTEXT('LOCALE_SAP'); do not hard-code 'E'.

━━━━━━━━━━━━━━━━━━━━
▶ 5. PARAMETERS VS CONSUMER WHERE
A WHERE condition written by the consumer is applied to the function result. It is not guaranteed to be pushed into the function body. So if a filter is selective (company code, key date, supplier), make it a parameter and apply it inside the SQLScript. Details in Part 4.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Add p_bukrs with a clear meaning; filter inside SQLScript.
Make the function client-dependent with #SESSION_VARIABLE; add the client filter to every client-dependent source.
Add a language parameter for any texts.
Pass all parameters through the wrapping view entity.
Test in two clients (or check that only the logon client appears).

✅ CHECKPOINT: your table function returns the right rows for the right client, date and language.

Code example

ABAP Code

// ===== CDS definition: parameters with defaults + client handling =====@ClientHandling.type: #CLIENT_DEPENDENT@ClientHandling.algorithm: #SESSION_VARIABLEdefine table function ZTF_PurchReqOpenBySupplier  with parameters    @Environment.systemField: #SYSTEM_DATE    p_keydate  : abap.dats,    @Environment.systemField: #SYSTEM_LANGUAGE    p_language : abap.lang,    p_bukrs    : abap.char(4)                 // selective filter -> parameter  returns {    client        : abap.clnt;                // first element    Supplier      : abap.char(10);    SupplierName  : abap.char(80);    OpenRequests  : abap.int4;    OldestDays    : abap.int4;  }  implemented by method zcl_tf_purchreq_aging=>get_open_by_supplier; " ===== SQLScript: client filter on EVERY client-dependent source =====METHOD get_open_by_supplier BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT                            OPTIONS READ-ONLY                            USING zpr_head zpr_supplier_t.  RETURN    SELECT h.client,           h.supplier                                   AS "SUPPLIER",           t.supplier_name                              AS "SUPPLIERNAME",           COUNT(*)                                     AS "OPENREQUESTS",           MAX( DAYS_BETWEEN( h.created_on, :p_keydate ) ) AS "OLDESTDAYS"      FROM zpr_head AS h      LEFT OUTER JOIN zpr_supplier_t AS t        ON  t.client   = h.client        AND t.supplier = h.supplier        AND t.language = :p_language                   -- no hard-coded 'E'     WHERE h.client  = SESSION_CONTEXT( 'CDS_CLIENT' ) -- client filter!       AND h.company_code = :p_bukrs                   -- parameter used inside       AND h.status  = 'S'     GROUP BY h.client, h.supplier, t.supplier_name;ENDMETHOD. // ===== Wrapping view passes parameters on =====// define view entity ZI_PurchReqOpenBySupplier//   with parameters p_bukrs : abap.char(4)//   as select from ZTF_PurchReqOpenBySupplier(//        p_keydate: $session.system_date,//        p_language: $session.system_language,//        p_bukrs: $parameters.p_bukrs )// { key Supplier, SupplierName, OpenRequests, OldestDays }

Real project scenario

In a multi-client test system, a supplier dashboard showed each supplier twice with different numbers. The table function had no client filter: rows from the test client and the training client were summed and returned together.
The team made the function client-dependent with #SESSION_VARIABLE and added SESSION_CONTEXT('CDS_CLIENT') filters on every source, including the text table join. A review checklist item "client filter on every client-dependent source in SQLScript" was added for all AMDP code.

Common mistakes

No client filter in SQLScript.
Client filter on the main table but not on joined tables.
Hard-coded language.
Selective filters as consumer WHERE instead of parameters.
Parameters not passed through wrapping views.
Missing session defaults, so services must supply every value.

Best practices

Declare client handling explicitly and filter every client-dependent source.
Use @Environment.systemField defaults for date, language, client.
Make selective filters parameters and apply them inside SQLScript.
Pass parameters through wrapping views.
Test with at least two clients.

Interview angle

Questions: "How is the client handled in a CDS table function?", "Why use parameters instead of WHERE?", "How do you default a parameter to today's date?"
Strong answer: SQLScript does not apply the client implicitly; declare @ClientHandling.type #CLIENT_DEPENDENT with algorithm #SESSION_VARIABLE, put client first in returns, and filter each client-dependent source with SESSION_CONTEXT('CDS_CLIENT'). Selective filters should be parameters applied inside the function because consumer WHERE conditions act on the result. Defaults via @Environment.systemField (#SYSTEM_DATE, #SYSTEM_LANGUAGE, #CLIENT).

Practice this lesson

Quick check

1. A table function shows data from two clients in a test system. What is missing?

2. A table function returns 50 rows but processes 8 million because the consumer filters by company code afterwards. What is the best fix?

3. How should consumers such as a RAP service access a CDS table function?

Interview questions

1. A supplier dashboard based on a table function shows each supplier twice in a test system. What is the likely cause?

Answer it in your own words first, then compare.

2. How do parameters and session defaults work in CDS table functions?