Create a Table function in SAP HANA

[probledo]

Dear Experts,

I am trying to create a Table function in SAP HANA . I am facing a problem while creating it.

I did a test through a SELECT and I get the expected result, now I have to implement this same logic in the Table Function, but I have an error in the code.

I have a simple Calculation View whose columns are PRODUCT, BENEFIT, CALDAY.

Thank you so much for your help!!

Regards.

This is the error:

Could not execute 'FUNCTION "SCHEMA_NAME"."ZTEST05_TAB_FUNCTION" ( ) RETURNS (PRODUCT NVARCHAR(2), BENEFIT NVARCHAR(20)) ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "FUNCTION": line 1 col 1 (at pos 1)

FUNCTION "SCHEMA_NAME"."ZTEST05_TAB_FUNCTION" ( ) 
	RETURNS (PRODUCT NVARCHAR(2), BENEFIT NVARCHAR(20))
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
SELECT
	 "PRODUCT",	 
	 COUNT(*), STRING_AGG(BENEFIT,',')
FROM "_SYS_BIC"."SCHEMA_NAME/ZCV_TEST05_1"('PLACEHOLDER' = ('$$ip_ym$$',
	 '2018-06-30')) 
GROUP BY "PRODUCT"         
END;

[pfefferf · Accepted solution]

Your table function is missing the "return" statement, because the table function has to return a result. Via the return statement a result has to be returned which matches the type of the defined return type (a table with a structure containing fields PRODUCT and BENEFIT). You can either combine that with with the select statement (if you remove the COUNT(*) and define an alias for the result of the STRING_AGG function or you define an intermediate result variable for the query result and return that intermediate result. An example would be:

...
BEGIN
  RETURN select "PRODUCT", STRING_AGG("BENEFIT", ',') as "BENEFIT"
         from "_SYS_BIC"."SCHEMA_NAME/ZCV_TEST05_1"('PLACEHOLDER' = ('$$ip_ym$$',
	 '2018-06-30')) 
         group by "PRODUCT";
END;

PS: Please also check always to close your statements with a semi-colon (like e.g. the query statement in your logic which is not closed).

[probledo · Accepted solution]

Thank you so much!! Florian.