Skip to Content
-3

Error while creating a UDF with table result

Aug 18, 2017 at 04:33 PM

73

avatar image
Former Member

Hi. Guys,

My requirement is I was trying to create a function which should show the result based on year selected as input parameter in a table result.

CREATE FUNCTION "SCH_XYZ"."FUN_TABLE_TYPE1" (IN I_YEAR INTEGER) RETURNS TABLE(COMP_NAME NVARCHAR, YEAR SMALLINT ,TOTAL_SALES INTEGER,QUANTITY SMALLINT)

LANGUAGE SQLSCRIPT

AS

BEGIN

RETURN

SELECT COMP_NAME, YEAR, TOTAL_SALES, QUANTITY FROM "SCH_XXX"."CUSTOMER SALES" WHERE YEAR ;

END;

If I execute this function with select option, I am getting total table output.

But my question here is how can display the result for certain year using this function.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Sergio Guerrero Aug 18, 2017 at 06:12 PM
1

in your select statement, in the where clause, add the input param

SELECT COMP_NAME, YEAR, TOTAL_SALES, QUANTITY FROM "SCH_XXX"."CUSTOMER SALES" WHERE YEAR = :I_YEAR; -- :IN_VARIABLE -- don't forget the :

you missed the input param. hope that is what you need

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 21, 2017 at 12:03 PM
0

Hi Arun,

If I understand your question correctly.This is what you need in your output.

You want to display the output based on Year you pass as input parameter.

You need to mention the input parameter in where condition to filter the values with required input.

CREATE FUNCTION "SCH_XYZ"."FUN_TABLE_TYPE1" (IN I_YEAR INTEGER) RETURNS TABLE(COMP_NAME NVARCHAR, YEAR SMALLINT ,TOTAL_SALES INTEGER,QUANTITY SMALLINT)

LANGUAGE SQLSCRIPT

AS

BEGIN

RETURN

SELECT COMP_NAME, YEAR, TOTAL_SALES, QUANTITY FROM "SCH_XXX"."CUSTOMER SALES" WHERE YEAR = :I_YEAR ;

END;

Hope this helps..!

Thanks,

Jay.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Sep 22, 2017 at 01:43 PM
0

Hi Arun,

In general of SAP HANA procedures, the input/output parameter should be referred using :<param_name>

In your case, refer the :I_YEAR in the where condition clause .

SELECT COMP_NAME, YEAR, TOTAL_SALES, QUANTITY FROM "SCH_XXX"."CUSTOMER SALES" WHERE YEAR = :I_YEAR ;

Regards,
Surya

Share
10 |10000 characters needed characters left characters exceeded