Symptom
Your SQL uses one of the SAP HANA–specific extensions of the GROUPING SETS clause, and SAP HANA Cloud now reports it as a deprecated feature. The affected extensions are:
BESTLIMIT … OFFSETWITH SUBTOTALWITH BALANCEWITH TOTALSTRUCTURED RESULTWITH OVERVIEWPREFIXMULTIPLE RESULTSETS
SAP's replacement guidance also covers TEXT_FILTER with FILL UP and SORT MATCHES TO TOP, so treat it the same way.
Example: a report runs
SELECT REGION, PRODUCT, SUM(REVENUE)
FROM SALES
GROUP BY GROUPING SETS LIMIT 10 WITH TOTAL ((REGION, PRODUCT));Before 2026 QRC 4 this is a supported statement. From 2026 QRC 4 on, it still returns the top 10 groups plus a total row, but each run is recorded as usage of a deprecated feature, and the syntax will stop working once SAP removes it.
The change goes unnoticed because the statements keep running: no error is raised and the results don't change. You only see it if you check feature usage. The typical trigger is the automatic upgrade of your SAP HANA Cloud instance to 2026 QRC 4.
Cause
These extensions are SAP HANA's own additions to GROUPING SETS and are not part of the SQL standard. SAP is retiring them because standard SQL can produce the same results and is more portable across tools and drivers.
Who is affected
All of the following must apply:
- You run SAP HANA Cloud 2026 QRC 4 or later.
- Your application code, views, procedures, reports or tool-generated SQL use at least one of the extensions listed above.
To see whether the database has executed any of them, run:
SELECT *
FROM SYS.M_FEATURE_USAGE
WHERE COMPONENT_NAME = 'SQL'
AND FEATURE_NAME IN ('GROUPING SETS BEST',
'GROUPING SETS LIMIT OFFSET',
'GROUPING SETS WITH SUBTOTAL',
'GROUPING SETS WITH BALANCE',
'GROUPING SETS WITH TOTAL',
'GROUPING SETS STRUCTURED RESULT',
'GROUPING SETS WITH OVERVIEW',
'GROUPING SETS PREFIX',
'GROUPING SETS MULTIPLE RESULTSETS');Each row that shows usage names an extension that has run on this instance. An empty result does not prove you are safe: the view only reflects statements that have actually executed, so month-end or year-end reports and rarely used procedures may not appear yet. Also search your code repositories, view and procedure definitions, and the SQL generated by reporting tools for GROUPING SETS.
Replace the deprecated syntax on 2026 QRC 4 and later
At the time of writing, SAP has deprecated these extensions but not yet removed them. Replace each one with standard SQL now. The templates below use <TABLE>, the grouping columns <DIM_1> and <DIM_2>, and the measure <MEASURE>.
WITH SUBTOTAL and WITH TOTAL without a limit
Add the empty grouping set (). It returns one extra row with the aggregate over all groups.
SELECT <DIM_1>, <DIM_2>, SUM(<MEASURE>)
FROM <TABLE>
GROUP BY GROUPING SETS ((<DIM_1>, <DIM_2>), ());For WITH TOTAL across several grouping sets, pair each set with (), combine them with UNION ALL and tag each block with a set ID so you can sort the result.
LIMIT … OFFSET
Number the groups with ROW_NUMBER() and filter on the row number. Run each grouping set separately and combine them with UNION ALL.
SELECT <DIM_1>, <DIM_2>, SUM_MEASURE
FROM (SELECT <DIM_1>, <DIM_2>,
SUM(<MEASURE>) AS SUM_MEASURE,
ROW_NUMBER() OVER (ORDER BY SUM(<MEASURE>) DESC, <DIM_2>, <DIM_1>) AS RN
FROM <TABLE>
GROUP BY <DIM_1>, <DIM_2>)
WHERE RN > <OFFSET>
AND RN <= <OFFSET> + <LIMIT>
ORDER BY RN;WITH SUBTOTAL, WITH BALANCE and WITH TOTAL with a limit
Compute the extra row separately:
- Subtotal (aggregate over the groups inside the limit): filter with
INon the top-<LIMIT>groups. - Balance (aggregate over the groups outside the limit): use the same query with
NOT IN. - Total: a plain aggregate over the whole table.
-- Subtotal; replace IN with NOT IN for the balance row
SELECT NULL AS <DIM_1>, NULL AS <DIM_2>, SUM(<MEASURE>)
FROM <TABLE>
WHERE (<DIM_1>, <DIM_2>) IN (
SELECT <DIM_1>, <DIM_2>
FROM (SELECT <DIM_1>, <DIM_2>,
ROW_NUMBER() OVER (ORDER BY SUM(<MEASURE>) DESC, <DIM_2>, <DIM_1>) AS RN
FROM <TABLE>
GROUP BY <DIM_1>, <DIM_2>)
WHERE RN <= <LIMIT>);
-- Total
SELECT NULL AS <DIM_1>, NULL AS <DIM_2>, SUM(<MEASURE>)
FROM <TABLE>;For WITH BALANCE without a limit, run each grouping set on its own and let the application insert the separator row of NULL values.
BEST
Split it into two steps. First count the groups in each candidate grouping set, then run only the sets the application selects.
-- Step 1: number of groups per candidate set (ASC for BEST -N)
SELECT 'D1_D2' AS SET_NAME, COUNT(*) AS CARD FROM (SELECT <DIM_1>, <DIM_2> FROM <TABLE> GROUP BY <DIM_1>, <DIM_2>)
UNION ALL
SELECT 'D1', COUNT(*) FROM (SELECT <DIM_1> FROM <TABLE> GROUP BY <DIM_1>)
UNION ALL
SELECT 'D2', COUNT(*) FROM (SELECT <DIM_2> FROM <TABLE> GROUP BY <DIM_2>)
ORDER BY CARD DESC;
-- Step 2: the application runs only the top N sets, for example BEST 2 = D1_D2 and D1
SELECT <DIM_1>, <DIM_2>, SUM(<MEASURE>) FROM <TABLE> GROUP BY <DIM_1>, <DIM_2>
UNION ALL
SELECT <DIM_1>, NULL AS <DIM_2>, SUM(<MEASURE>) FROM <TABLE> GROUP BY <DIM_1>;TEXT_FILTER, FILL UP and SORT MATCHES TO TOP
Convert the wildcards (* → %, ? → _) and filter with LIKE. Use a CASE expression for FILL UP and ORDER BY CASE for SORT MATCHES TO TOP.
SELECT <DIM_1>, <DIM_2>, SUM(<MEASURE>),
CASE WHEN CAST(<DIM_2> AS VARCHAR) LIKE '<PATTERN>' THEN <DIM_2> END AS TEXT_FILTER_DIM_2
FROM <TABLE>
GROUP BY <DIM_1>, <DIM_2>
ORDER BY CASE WHEN CAST(<DIM_2> AS VARCHAR) LIKE '<PATTERN>' THEN 0 ELSE 1 END,
<DIM_1>, <DIM_2>;Without FILL UP, move the LIKE condition into the WHERE clause instead.
STRUCTURED RESULT, WITH OVERVIEW, PREFIX and MULTIPLE RESULTSETS
Replace STRUCTURED RESULT with a SQLScript procedure that writes each grouping set into its own local temporary table. The prefix parameter takes over the role of PREFIX.
CREATE PROCEDURE <PROCEDURE_NAME> (IN PREFIX NVARCHAR(50) DEFAULT '#GN')
LANGUAGE SQLSCRIPT AS
BEGIN
EXEC 'CREATE LOCAL TEMPORARY TABLE "' || :PREFIX || '1" AS
SELECT <DIM_1>, <DIM_2>, SUM(<MEASURE>) AS SUM_MEASURE FROM <TABLE> GROUP BY <DIM_1>, <DIM_2>';
EXEC 'CREATE LOCAL TEMPORARY TABLE "' || :PREFIX || '2" AS
SELECT <DIM_1>, SUM(<MEASURE>) AS SUM_MEASURE FROM <TABLE> GROUP BY <DIM_1>';
END;For MULTIPLE RESULTSETS, send separate queries from the application or use a procedure WITH RESULT VIEW:
CREATE PROCEDURE <PROCEDURE_NAME> ()
LANGUAGE SQLSCRIPT WITH RESULT VIEW <RESULT_VIEW_1>, <RESULT_VIEW_2> AS
BEGIN
SELECT <DIM_1>, <DIM_2>, SUM(<MEASURE>) AS SUM_MEASURE FROM <TABLE> GROUP BY <DIM_1>, <DIM_2>;
SELECT <DIM_1>, SUM(<MEASURE>) AS SUM_MEASURE FROM <TABLE> GROUP BY <DIM_1>;
END;Keep in mind:
- The deprecated syntax still runs, so nothing breaks immediately. Plan the rewrite before SAP removes the extensions.
- Add a deterministic tie-breaker to every
ROW_NUMBER()ordering, as in the templates. Otherwise groups with equal aggregates can move in and out of the limit between runs. NOT INreturns no rows if the subquery containsNULLgrouping values. If your grouping columns can beNULL, useNOT EXISTSinstead.- SQL generated by reporting tools or drivers may use these extensions without you writing them. Check with the tool vendor whether a newer version generates standard SQL.
- The deprecation applies to SAP HANA Cloud 2026 QRC 4 and later. SAP doesn't list SAP HANA on-premise as affected, but rewriting to standard SQL also keeps on-premise code portable for a later move to the cloud.
Confirm the result
- Before you switch, run the old and the new statement on the same data and compare row counts, totals and the order of the top groups.
- Check the subtotal, balance and total rows in particular. They are where the rewritten logic differs most from the original.
- After deployment, rerun the
M_FEATURE_USAGEquery over a full business cycle, including month-end and year-end runs, and confirm that no new usage appears. - Review downstream consumers such as reports, dashboards, extracts and cached result sets that relied on the old result layout (separator rows, result-set names, prefixes).