unable to launch scalar function from within view

[former_member207606]

Hi

I would like to extend an existing view with an additional column. This column should be calculated via a scalar funktion.

The existing view returns sth. like:

value1	value2
1	1
2	2
3	3

The new view is supposed to return sth. like this:

value1	value2	scalarFunctionResult
1	1	4
2	2	5
3	3	6

The trick is: The input of the scalar function is supposed to be the content of "value 1"

The code looks sth. like this. (simplified and not tested)

CREATE VIEW "schema"."namespace::MyView" ( "value1",

      "value 2",

      "scalarFunctionResult"

      ) AS SELECT

      "test"."value1"  ,

      "test"."value2"  ,     

      "schema"."namespace::myScalarFunction("value1")

FROM "schema"."namespace::ExistingView AS "test"  ;

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

FUNCTION "schema"."namespace::myScalarFunction ( value varchar(64) )

RETURNS newValue float LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN   

    select (value1+3) into newValue from dummy;

END

However this does not work. So I might think the wrong way, or there is a mistake in referencing the value1

The hana error is the following: (dberror) 2048 - column store error: column store error: search table error: [6900] Attribute engine failed;L execution failed with rc=20787,

Any ideas?

[pfefferf · Accepted solution]

Hello Bernd,

in general it works to use a scalar function in a view definition.

In your CREATE VIEW statement the closing double quotes for the scalar function and the view you are selecting from are missing.

Regards,

Florian

[former_member207606 · Accepted solution]

Hi Florian

Unfortunately this was just a typo here in the forum.

I narrowed it down a bit further: Actually: "test"."value1" would be the right syntax to pass the value to the scalar function.

In the function I have tried to just return the value - which works, also a simple calculation works.

Then I have tried to use a function similar to:

FUNCTION "calculateAge" ( ID VARBINARY(32) )

RETURNS Age VARBINARY(32) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

   DECLARE currentDate DATE;

   DECLARE birthDate DATE;

  currentDate:=current_date;

  select "birthdate" into birthDate from "MyTable" where "bID" = :ID ;

  select (round(days_between(:birthDate ,:currentDate )/365)) into Age from dummy;

END

And this is where I get my error:

SAP DBTech JDBC: [2048]: column store error: search table error:  [6900] Attribute engine failed;L execution failed with rc=20787, "SAP_HPH"."sap.plugins.mimic.db.functions::calculateAge": line 19 col 5 (at pos 1041): [1299] (range 3) no data found exception: no data found,raw [here]eval_i(string unused_constant(), string unused_constant(), string unused_constant(), string unused_constant(), raw "SYSTEM.#_SYS_QO_COL_7f292a49a640:6000000000c496c.DWID"),SYSTEM.#_SYS_QO_COL_7f292a49a640:6000000000c496c.DWID = 'MIMICPRESCRIPTION_1026619'[raw]

The same statement with a hard coded value:

select "birthdate" into birthDate from "MyTable" where "bID" = '100';

Works absolutely fine...

I made sure the the data types match in my example.

[former_member207606 · Accepted solution]

The solution was:

My query came back with no result. And the scalar function required the SQL Query to respond with a  valid result or handle the SQL error 1299.

i.e. I had to declare my variable birthDate with an error handler:

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299

    BEGIN

        birthDate := NULL;

    END;