Skip to Content

unable to launch scalar function from within view

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Apr 21, 2016 at 03:03 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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;

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.