What is FreeLearning365?

FreeLearning365 is a free learning platform offering daily tutorials on technology, programming, and certifications.

HANA SQLScript Procedures, Table Functions & Real AR Aging from ACDOCA – Day 2 Hands-On | Part 47 | FreeLearning365

🔥 DAY02 [SAP HANA Database & Performance] {Part-47}

Welcome back to the S/4HANA Data Engineer Track. Yesterday you absorbed the HANA architecture: in‑memory, column store, delta buffer. Today we pick up the tools that actually make those columns work for you. This is where you stop being a passive SQL user and become a database logic engineer. You will write procedures that live inside HANA, table functions that behave like virtual tables, and use language features that leave traditional ANSI SQL in the dust.

By the end of today, you’ll understand why SAP moved so much application logic into the database layer, and you’ll have a ready‑to‑run SQLScript procedure that calculates aged accounts receivable buckets directly from ACDOCA. No ABAP report, no BW extraction – pure HANA power. Let’s unlock it.

📜 HANA SQL Dialect vs ANSI SQL – The 7 Differences That Will Make or Break Your Query

SAP HANA supports a superset of ANSI SQL-92/99/2003. But “superset” doesn’t mean you can just write Oracle SQL and hope it runs. The HANA engine has its own optimizations, data types, and syntax extensions. Learning these differences prevents performance disasters and syntax errors that waste hours.

1. Implicit cast and data type strictness

ANSI SQL often allows implicit conversion between numeric and string. HANA is stricter with type checking in certain contexts, especially inside SQLScript procedures. For example, assigning a VARCHAR to an integer variable without explicit CAST will throw an error. This strictness avoids silent data corruption.

2. No empty string = NULL (the great trap)

In HANA, an empty string ('') is treated as NULL. This differs from Oracle where empty string is NULL, but from SQL Server where it’s distinct. If your ECC code relied on field = '' to find blank values, they are now NULL. Always use IS NULL or COALESCE.

3. LIMIT and OFFSET instead of TOP / ROWNUM

HANA uses SELECT ... LIMIT 10 OFFSET 5. No ROWNUM like Oracle, no TOP like SQL Server. This is cleaner and aligns with PostgreSQL style.

4. Window functions – fully supported but with HANA flavour

HANA supports advanced window functions (RANK, LEAD, LAG, rolling sums) with ROWS/RANGE framing. But the optimizer can push window computation into the column engine aggressively. Write them without fear, but be aware that huge partitions can consume memory; partitioning by month on ACDOCA works beautifully.

5. String concatenation: || operator and CONCAT function

You can use both. 'A' || 'B' works. CONCAT('A','B') as well. No + like in SQL Server.

6. Temporal tables and system‑versioned tables

HANA has native support for system‑versioned and application‑time period tables, far beyond typical ANSI. S/4HANA uses these for some master data but not for transactional tables. Still, you might encounter them in custom apps.

7. SQLScript imperative constructs

The biggest difference: you can write IF/ELSE, WHILE loops, cursors, and exception handling directly in SQL blocks. This blurs the line between SQL and a procedural language. But use loops sparingly – always aim for set‑based operations first.

💡 Pro tip: Before you write a WHILE loop, ask “can I express this with a CE function or a window aggregate?” If yes, do that – it will be parallelised automatically by the HANA plan executor. Loops are single‑threaded and can destroy performance on large data.

⚙️ SQLScript: Imperative vs Declarative – The Two Faces of HANA Logic

SQLScript is SAP’s proprietary extension that lets you write data‑intensive logic inside HANA. It has two modes: declarative (set‑oriented, functional) and imperative (step‑by‑step, procedure‑like). Knowing when to use each is the hallmark of a skilled HANA developer.

🧩 Declarative SQLScript – CE Functions (the “what, not how”)

The Calculation Engine (CE) provides special built‑in functions that tell HANA what you want, and the engine figures out the optimal how. CE functions include CE_JOIN, CE_UNION_ALL, CE_AGGREGATION, CE_PROJECTION. They are compiled into column‑engine operations and can be pushed down to the OLAP engine. For BW‑style star schema joins, CE functions often outperform raw SQL.

-- Declarative example: using CE_JOIN to combine two tables
OUT = CE_JOIN(
  LEFT_TABLE => :input_data1,
  RIGHT_TABLE => :input_data2,
  JOIN_TYPE => 'INNER',
  JOIN_CONDITION => 'LEFT.COMPANY = RIGHT.COMPANY'
);

However, CE functions are considered legacy for new development; SAP now recommends SQL/ABAP CDS views. But inside SQLScript procedures you may still encounter them, and they can perform better in mixed scenarios.

🧠 Imperative SQLScript – Variables, Loops, IF/ELSE, Cursors

Here you write explicit control flow. This is necessary for business logic that requires multi‑step calculations, error handling, or dynamic table names. The example we’ll build for AR aging uses a mix of declarative selects and imperative assignment.

CREATE PROCEDURE PROC_DEMO (OUT result TABLE(...))
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE v_total INTEGER;
  SELECT COUNT(*) INTO v_total FROM my_table;
  IF v_total > 1000 THEN
    -- do something
  END IF;
END;

⚖️ The golden rule

Use declarative (SQL/CALC views) for 90% of your logic. Only drop into imperative when you must – row‑by‑row processing on millions of rows is a performance killer. HANA can execute a single INSERT..SELECT in parallel; a cursor‑based update will serialise everything.

📦 Stored Procedures – Input/Output Params, Error Handling & the SIGNAL Art

SQLScript procedures are the workhorses of HANA backend logic. You can call them from ABAP via EXEC SQL, from XSJS, or from other procedures. They can return multiple result sets, scalar values, or both. But the real power is in parameterization and error management.

📥 Input parameters

You can define scalar inputs with type and optional default values. For example, a date range for the AR aging.

CREATE PROCEDURE GET_AGING (
  IN p_key_date DATE,
  IN p_company_code NVARCHAR(4)
) ...

📤 Output parameters – table types and scalar

Procedures can output one or more table‑typed parameters. These behave like result sets that can be consumed by applications or nested in other SQL.

OUT out_aging TABLE(
  CUSTOMER NVARCHAR(10),
  BUCKET NVARCHAR(20),
  AMOUNT DECIMAL(18,2)
)

You can also have multiple output tables. This is brilliant for returning related datasets in a single call.

🛑 Error handling: SIGNAL & RESIGNAL

SQLScript uses SIGNAL and RESIGNAL to raise exceptions. This is not like Oracle’s RAISE_APPLICATION_ERROR but works similarly. You can set a custom SQL error code and message.

IF :v_count = 0 THEN
  SIGNAL SQL_ERROR_CODE 10000 SET MESSAGE_TEXT = 'No data for given key date';
END IF;

You can catch exceptions with DECLARE EXIT HANDLER FOR SQLEXCEPTION. This allows rollback or logging.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- log error, then resignal
  INSERT INTO error_log VALUES (CURRENT_TIMESTAMP, ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
  RESIGNAL;
END;

This pattern is essential for production‑grade procedures. Always anticipate that a division by zero or a missing date parameter can break your flow.

📝 Lab preview: The AR aging procedure will use all of this – parameters for key date, company, and return table of buckets.

🍽️ Table Functions – The Supercharged Views with Parameters

A table function is a SQLScript function that returns a table and can be used directly in the FROM clause of a SELECT. Think of it as a parameterized view. No need to create a temporary table or call a procedure. This makes them extremely composable.

🔹 Scalar functions vs table-valued functions

Scalar function: returns a single value, can be used in SELECT list. Example: GET_CUSTOMER_RISK(CUSTOMER_ID).
Table-valued function: returns a table, placed in FROM. Example: SELECT * FROM GET_AGING_TABLE('20260131','1000').
Parameterized table function: exactly what we need to build a reusable AR aging view.

🏗️ Anatomy of a table function

CREATE FUNCTION GET_OPEN_ITEMS (
  IN p_company NVARCHAR(4),
  IN p_keydate DATE
)
RETURNS TABLE (
  CUSTOMER NVARCHAR(10),
  DUE_DATE DATE,
  AMOUNT DECIMAL(18,2)
) AS
BEGIN
  RETURN
    SELECT CUSTOMER, DUE_DATE, AMOUNT
    FROM ACDOCA
    WHERE COMPANY_CODE = :p_company
      AND POSTING_DATE <= :p_keydate
      AND CLEARING_DATE IS NULL; -- open items
END;

Now any report can call this function like a table. The HANA optimizer can push additional WHERE conditions into the function, making it highly efficient. This is far superior to copying the same logic into multiple CDS views.

✅ Best Practice: Encapsulate reusable business logic (like fiscal period calculations, aging buckets, currency conversions) in table functions. They are testable, versionable, and reduce ABAP code. Many S/4HANA embedded analytics use this pattern.

📊 Example: embedding a table function in a CDS view

You can define a CDS view that consumes a HANA table function via @ObjectModel.tableFunction.name in ABAP CDS. This is how embedded analytics Fiori apps call native HANA logic.

📈 Series Data Functions – Time‑Series Intelligence Without Leaving the Database

HANA includes built‑in support for series data generation and analysis. This is incredibly useful for filling gaps in time series (e.g., missing days in sales), generating date ranges, or windowing. You don’t need to export to Python for a simple time‑series aggregation.

GENERATE_SERIES – create rows on the fly

-- Generate all dates in January 2026
SELECT GENERATED_PERIOD_START AS DAY_DATE
FROM SERIES_GENERATE_DATE('2026-01-01', '2026-01-31', 'INTERVAL 1 DAY');

You can cross‑join this with your fact data to ensure every day appears, even with zero sales – critical for continuous time charts.

Time‑series aggregates using window functions

Combining GENERATE_SERIES with window functions gives you moving averages, cumulative sums, and year‑over‑year comparisons in a single SQL statement.

SELECT DAY_DATE,
       SUM(AMOUNT) OVER (ORDER BY DAY_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WEEKLY_ROLLING_SUM
FROM sales_series;

In the AR aging lab, we won’t use series generation but you can incorporate it to produce a daily aging snapshot history – we’ll touch on that as an extension.

🔍 Full‑Text Search: CONTAINS, Fuzzy Search & Text Indexes – Making Unstructured Data Findable

S/4HANA stores a lot of structured data, but also textual content: customer notes, material descriptions, long text. HANA’s full‑text engine allows you to search across millions of text fields as easily as searching Google.

📚 Creating a full‑text index

CREATE FULLTEXT INDEX FTX_MATERIAL_DESC ON MATERIAL(DESCRIPTION)
TEXT ANALYSIS ON;

Once the index is built, you can use CONTAINS predicate.

🔎 CONTAINS basics

SELECT MATERIAL, DESCRIPTION
FROM MATERIAL
WHERE CONTAINS(DESCRIPTION, 'stainless AND bolt', 'LINGUISTIC');

You can use fuzzy search with FUZZY(0.8) to tolerate typos – extremely useful when users type "aluminim" instead of "aluminium".

SELECT * FROM customer_notes
WHERE CONTAINS(NOTE_TEXT, 'FUZZY(0.7) "complaint"', 'LINGUISTIC');

⚡ Real‑life scenario: Fiori app search with text index

Imagine a credit controller searching for all notes containing "dispute". Without a full‑text index, every character search would scan the entire note column. With an index, the search completes in milliseconds, even on millions of rows. Always consider full‑text indexes for any column used in free‑text search in Fiori apps.

🧪 Hands‑On Lab: Build the Aged Accounts Receivable Buckets Procedure from ACDOCA

Now we put everything together. You will create a complete SQLScript procedure that reads open items from ACDOCA (the universal journal) and buckets them by days overdue: 0–30, 31–60, 60–90, 90+. This is the real logic used by finance departments every day.

📋 Business requirement

As a credit manager, I need a report showing, for a given key date and company code, the total open AR amounts grouped by aging bucket. The data must come from ACDOCA, not from BSID/BSAD (since those might not exist in S/4HANA). We assume that open items are those with a non‑zero receivable balance and clearing date NULL.

🧱 Step‑by‑step design

Input: p_key_date (DATE), p_company_code (NVARCHAR(4)).
Logic: Select open items where POSTING_DATE <= key date, clearing is not done. Calculate days overdue as KEY_DATE - DUE_DATE.
Bucketing: Use CASE expression to assign bucket strings.
Output: Table with CUSTOMER, BUCKET, TOTAL_AMOUNT.
Error handling: If no data found, SIGNAL an informative error, but we might choose to return empty set instead – we’ll use a warning approach.

💾 Sample data (for local testing)

Assume your ACDOCA has these columns relevant: RCLNT (client), RLDNR (ledger), RBUKRS (company code), KUNNR (customer), BLDAT (document date), BUDAT (posting date), DUE_DATE, WAERS (currency), WSL (amount in document currency), AUGDT (clearing date), etc. We simplify to S/4HANA Finance standard fields.

-- Example records in ACDOCA (simplified)
('1000','CUST01','2026-01-15','2026-02-15',1000.00,NULL)
('1000','CUST02','2026-02-01','2026-03-01',2500.00,NULL)
('1000','CUST01','2026-03-10','2026-04-10', 500.00,'2026-04-05') -- cleared, ignore

⚙️ The SQLScript procedure code

CREATE OR REPLACE PROCEDURE PROC_AR_AGING (
  IN p_key_date DATE,
  IN p_company_code NVARCHAR(4),
  OUT out_aging TABLE (
    CUSTOMER NVARCHAR(10),
    AGING_BUCKET NVARCHAR(20),
    TOTAL_AMOUNT DECIMAL(18,2)
  )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  DECLARE v_rows INTEGER;

  -- Ensure key date is not null
  IF p_key_date IS NULL THEN
    SIGNAL SQL_ERROR_CODE 10001
    SET MESSAGE_TEXT = 'Key date must be provided';
  END IF;

  -- Create a temporary view of open items with days overdue
  out_aging =
    SELECT 
      KUNNR AS CUSTOMER,
      CASE
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 0 AND 30 THEN '0-30 Days'
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 31 AND 60 THEN '31-60 Days'
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 61 AND 90 THEN '61-90 Days'
        ELSE '90+ Days'
      END AS AGING_BUCKET,
      SUM(WSL) AS TOTAL_AMOUNT
    FROM ACDOCA
    WHERE RBUKRS = p_company_code
      AND BUDAT <= p_key_date
      AND AUGDT IS NULL          -- not yet cleared
      AND WSL > 0               -- positive receivable
      AND KOART = 'D'           -- customer line
    GROUP BY KUNNR, 
      CASE
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 0 AND 30 THEN '0-30 Days'
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 31 AND 60 THEN '31-60 Days'
        WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 61 AND 90 THEN '61-90 Days'
        ELSE '90+ Days'
      END;

  -- Get count of result rows
  SELECT COUNT(*) INTO v_rows FROM :out_aging;
  
  -- Optional: if no open items, we can either return empty set or signal a warning
  -- We choose to return empty set silently, but log a message via a debug table (optional)
  IF v_rows = 0 THEN
    -- just nothing; you could insert into a log if desired
  END IF;
END;

Let’s dissect the important parts:

DAYS_BETWEEN: HANA’s built‑in function to compute difference in days. No need to use DATE functions manually.
CASE expression in GROUP BY: Must repeat the exact CASE in SELECT and GROUP BY, or use a subquery. We repeated it. Another approach: compute days_overdue in a subquery then group by bucket.
KOART = 'D': Filters customer lines. ACDOCA holds many account types; this restricts to Accounts Receivable customers.
WSL > 0: Assumes debit balances for AR. Adjust sign logic according to your financial posting logic.

🔄 Alternative with table function (even cleaner)

CREATE FUNCTION TF_AR_AGING (
  p_key_date DATE,
  p_company_code NVARCHAR(4)
)
RETURNS TABLE (
  CUSTOMER NVARCHAR(10),
  AGING_BUCKET NVARCHAR(20),
  TOTAL_AMOUNT DECIMAL(18,2)
)
AS
BEGIN
  RETURN
    SELECT CUSTOMER, AGING_BUCKET, SUM(WSL) AS TOTAL_AMOUNT
    FROM (
      SELECT KUNNR AS CUSTOMER,
             DAYS_BETWEEN(DUE_DATE, p_key_date) AS DAYS_OVERDUE,
             CASE
               WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 0 AND 30 THEN '0-30 Days'
               WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 31 AND 60 THEN '31-60 Days'
               WHEN DAYS_BETWEEN(DUE_DATE, p_key_date) BETWEEN 61 AND 90 THEN '61-90 Days'
               ELSE '90+ Days'
             END AS AGING_BUCKET,
             WSL
      FROM ACDOCA
      WHERE RBUKRS = p_company_code
        AND BUDAT <= p_key_date
        AND AUGDT IS NULL
        AND KOART = 'D'
    ) sub
    GROUP BY CUSTOMER, AGING_BUCKET;
END;

Now you can query SELECT * FROM TF_AR_AGING('2026-03-31','1000') and get instant results. This is perfect for embedding in CDS views or Fiori OData services.

📊 Realistic scenario with sample data and output

Assume key date = 2026-03-31. Open items:

CUST01, DUE 2026-02-15, AMT 1200.00
CUST01, DUE 2026-04-10, AMT 800.00  (not overdue yet, days overdue = -10, so 0-30 bucket via BETWEEN works as overdue 0-30? Need to adjust logic: if DAYS_BETWEEN is negative, it’s not overdue. We should treat negative as 0 for bucketing or use a separate "Current" bucket.)

Our logic currently uses DAYS_BETWEEN(DUE_DATE, p_key_date) = (due - key). If due is after key date, days_between returns negative. The BETWEEN 0 AND 30 would exclude negative. That’s correct: they are not yet overdue, but they are open items. Many aging reports put those in "Not Due" or "Current". You can extend the CASE to handle negative as a separate bucket. We leave that as an enhancement for you.

🧪 Testing and performance considerations

Run an explain plan using PlanViz (we’ll cover more in Part 50). Ensure the ACDOCA partition pruning is working – filter on BUDAT and RBUKRS.
For high‑volume environments, ensure the procedure is executed with a key date range that doesn’t cause a massive delta merge storm. Already we learned about delta buffer yesterday – this query will read main and delta transparently.
You can schedule this procedure via a job or call it from an ABAP report using EXEC SQL.

🔁 Pros, Cons & Alternatives to This Approach

✅ Pros

Zero data duplication: No need for custom aging tables. Directly on ACDOCA.
Real‑time: Always up‑to‑date open items, no batch extracts.
Reusable: Table function can be consumed by multiple reports, CDS views, and Fiori apps.
Fast: Column‑store scanning and aggregation in HANA completes in sub‑seconds.

⚠️ Cons

Complexity: Business users may not understand the logic; need documentation.
Sign convention risks: Getting the debit/credit sign correct for AR requires finance expertise.
ACDOCA size: In very large systems (10+ billion rows), the query may still be heavy; consider pre‑aggregated cubes if performance lags. But with partitioning and proper filters, it’s rarely needed.

🔄 Alternatives

ABAP report reading BSID/BSAD tables – but they might be empty in S/4HANA if universal journal only.
BW extractor / BW/4HANA DataStore Object – introduces latency.
CDS view with parameters – similar to table function, but limited to ABAP stack.

📌 What We Built Today – Your SQLScript Toolkit Expands

From HANA SQL nuances to error‑resilient procedures and table functions, you’ve equipped yourself with the core programming model of the HANA database. The AR aging lab isn’t a toy; it’s a real deliverable that finance teams rely on. Tomorrow, we’ll advance to HANA data modeling with calculation views and hierarchies, but you’ll see that the table functions and SQLScript you learned today are the building blocks of those graphical models.

🔥 Action Item: Log into your HANA system (sandbox or dev) and run the table function TF_AR_AGING. Modify it to include a "Current" bucket for not‑yet‑overdue items. Experiment with full‑text search on some text columns. The only way to master HANA is to code, fail, and debug.

FreeLearning365

HANA SQLScript Procedures, Table Functions & Real AR Aging from ACDOCA – Day 2 Hands-On | Part 47 | FreeLearning365