Skip to Content
avatar image
-3
Former Member

Error while creating a UDF with table result

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Aug 18, 2017 at 06:12 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 21, 2017 at 12:03 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 22, 2017 at 01:43 PM

    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

    Add comment
    10|10000 characters needed characters exceeded