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:

  • BEST
  • LIMIT … OFFSET
  • WITH SUBTOTAL
  • WITH BALANCE
  • WITH TOTAL
  • STRUCTURED RESULT
  • WITH OVERVIEW
  • PREFIX
  • MULTIPLE 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

SQL
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:

SQL
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.

SQL
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.

SQL
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 IN on 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.
SQL
-- 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.

SQL
-- 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.

SQL
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.

SQL
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:

SQL
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 IN returns no rows if the subquery contains NULL grouping values. If your grouping columns can be NULL, use NOT EXISTS instead.
  • 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_USAGE query 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).