I have written some custom logic in HANA anonymous block. I used grouping function somewhere in the middle as a core. It worked well and the result was satisfying.
Once I tried to industrialize it by moving to HANA Table Function, the activation went well however function started throwing an error like below on attempt to select from it.
Could not execute 'udf_foo( )' SAP DBTech JDBC: [7]: feature not supported: grouping_id() or grouping() functions should be used with grouping sets
Could you please tell if grouping( ) operation is officially not supported by Table UDF on HANA?
I am using HANA 1.0 SPS 12. So far as a workaround I used case statements.
Way to reproduce:
do begin sequential execution
create column table t1 ( id int primary key, customer varchar(5), year int, product varchar(5), sales int );
insert into t1 values(1, 'C1', 2009, 'P1', 100);
insert into t1 values(2, 'C1', 2009, 'P2', 200);
insert into t1 values(3, 'C1', 2010, 'P1', 50);
insert into t1 values(4, 'C1', 2010, 'P2', 150);
insert into t1 values(5, 'C2', 2009, 'P1', 200);
insert into t1 values(6, 'C2', 2009, 'P2', 300);
insert into t1 values(7, 'C2', 2010, 'P1', 100);
insert into t1 values(8, 'C2', 2010, 'P2', 150);
end;
CREATE FUNCTION udf_grp_test ( )
RETURNS TABLE(
CUSTOMER NVARCHAR(2) ,
YEAR INT,
PRODUCT NVARCHAR(2) ,
GRP_YEAR NVARCHAR(1) ,
SALES DEC(3,0)
)
AS BEGIN
RETURN
SELECT
CUSTOMER,
YEAR,
PRODUCT,
GROUPING( YEAR ) AS GRP_YEAR,
SUM(SALES) AS SALES
FROM
T1
GROUP BY GROUPING SETS(
( CUSTOMER, YEAR),
( CUSTOMER, PRODUCT)
);
END
-- Works well
SELECT
CUSTOMER,
YEAR,
PRODUCT,
GROUPING( YEAR ) AS GRP_YEAR,
SUM(SALES)
FROM
T1
GROUP BY GROUPING SETS(
( CUSTOMER, YEAR),
( CUSTOMER, PRODUCT)
)
-- Throws error
SELECT * FROM udf_grp_test ( )
ps. I found out all the bugs reported in https://answers.sap.com/questions/11570257/sap-hana-usage-of-grouping.html are still not fixed in SPS 12.
