Error while creating a function in HANA

[Former Member]

I need help with an error that comes after I do a select statement for a function that I created.

This is the function

CREATE FUNCTION "PTIRUCHANUR"."ufnGetAllCategories"(retCategoryInformation INT)

RETURNS table (

   "PARENTPRODUCTCATEGORYNAME" NVARCHAR(50) ,

    "PRODUCTCATEGORYNAME" NVARCHAR(50),

      "PRODUCTCATEGORYID" INTEGER

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS 

-- Returns the CustomerID, first name, and last name for the specified customer.

BEGIN

      RETURN  Select

      "PARENTPRODUCTCATEGORYNAME",

      "PRODUCTCATEGORYNAME",

      "PRODUCTCATEGORYID"

      from"PTIRUCHANUR"."VGETALLCATEGORIES"

      Select * from "PTIRUCHANUR"."ufnGetAllCategories" ('5')

    where  :retCategoryInformation = "PRODUCTCATEGORYID";

END;

The error that I get when I do

Select * from "PTIRUCHANUR"."ufnGetAllCategories" ('5')

is as follows

Could not execute 'Select * from "PTIRUCHANUR"."ufnGetAllCategories" ('5')' in 44 ms 838 µs . SAP DBTech JDBC: [2048]: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$retCategoryInformation$$ is not set.

Please help me with this.

Thanks.

[former_member184768]

Hi Prasanth,

I tried to simulate your code with one of the tables in my schema and the code works fine for me. Can you please try to drop your function and recreate again. Also to make it simple first time, try to use everything in upper case and not prefix with the schema name. Not that it should matter, but just to keep it simplified.

Please note that I am on revision 55.

Regards,

Ravi

CREATE FUNCTION  FN_R_TEST (v_in INT)

RETURNS table ( "ZA" VARCHAR(5), "ZB" NVARCHAR(5), "ZC" integer)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN

      RETURN  Select B "ZA", C "ZB", D "ZC" from R_MY_TABLE

      where  :v_in = "A";

END;

select * from FN_R_TEST ('8');

[Former Member]

I actually created a similar function with the same logic and this is the sql

CREATE FUNCTION "PTIRUCHANUR"."ufnGetCustomerInformation"  (customerid int)

RETURNS table ("CUSTOMERID" INT,

"FIRSTNAME" NVARCHAR(30)  ,

     "LASTNAME" NVARCHAR(30)

)   LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS  

-- Returns the CustomerID, first name, and last name for the specified customer.

BEGIN

    RETURN SELECT

       "CUSTOMERID",

        "FIRSTNAME",

        "LASTNAME"

    FROM "PTIRUCHANUR"."CUSTOMER"

    WHERE "CUSTOMERID" = :customerid;

END;

This function works when I do a select statement.

Also I created a another function which is giving me the same error as my question and the sql is as follows.

CREATE FUNCTION "HALLE"."UFNGETSALESORDERSTATUSTEXT_TEST"(status tinyint)

RETURNS TABLE("RET" nvarchar(15))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

-- Returns the sales order status text representation for the status value.

BEGIN

    DECLARE "RET" nvarchar(15);

    RETURN SELECT

        CASE status

            WHEN 1 THEN 'In process'

            WHEN 2 THEN 'Approved'

            WHEN 3 THEN 'Backordered'

            WHEN 4 THEN 'Rejected'

            WHEN 5 THEN 'Shipped'

            WHEN 6 THEN 'Cancelled'

            ELSE '** Invalid **'

        END

    AS "RET"

    FROM DUMMY;

END;

the function executed correctly however the select statement is throwing the error referred in my original question

[kai-christoph_mueller]

Hi Prasant,

the following works right here:

CREATE FUNCTION "PLAYGROUND"."UFNGETSALESORDERSTATUSTEXT_TEST"(status tinyint)
RETURNS TABLE("RET" nvarchar(15))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE "RET" nvarchar(15);
    RETURN SELECT
        CASE status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END
    AS "RET"
    FROM DUMMY;
END;
select * from "PLAYGROUND"."UFNGETSALESORDERSTATUSTEXT_TEST"(1)

----------------

Statement 'CREATE FUNCTION "PLAYGROUND"."UFNGETSALESORDERSTATUSTEXT_TEST"(status tinyint) RETURNS TABLE("RET" ...' 
successfully executed in 169 ms 814 µs  (server processing time: 166 ms 179 µs) - Rows Affected: 0 
Statement 'select * from "PLAYGROUND"."UFNGETSALESORDERSTATUSTEXT_TEST"(1)' 
successfully executed in 35 ms 990 µs  (server processing time: 29 ms 247 µs)

I also think this is related to your system. Which Revision are you using?

-> You can check this by double clicking on the system in tab overview.

Best regards

kc

[sudha_rao2]

This message was moderated.

[former_member182114]

Hi Prasanth,

The error is valid as there are two SQL's on your code and the second seems to have no correlation with first one.  Don't understand what you are trying to do.

Select

      "PARENTPRODUCTCATEGORYNAME",

      "PRODUCTCATEGORYNAME",

      "PRODUCTCATEGORYID"

      from"PTIRUCHANUR"."VGETALLCATEGORIES"

----

      Select * from "PTIRUCHANUR"."ufnGetAllCategories" ('5')   where  :retCategoryInformation = "PRODUCTCATEGORYID";

----

Regards, Fernando Da Rós

[Former Member]

Sorry I mean to delete that. I do not have that in the original statement.

----

      Select * from "PTIRUCHANUR"."ufnGetAllCategories" ('5')   where  :retCategoryInformation = "PRODUCTCATEGORYID";

----

That select statement should not exist.