Strange error when using a user-defined scalar function within INSERT statement

[Former Member]

I have defined the simple 'min_value' function below, which I have been using for some time in SQL SELECT statements.

CREATE FUNCTION "MIN_VALUE" (v1 DECIMAL, v2 DECIMAL) RETURNS result DECIMAL

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN

    IF v1 <= IFNULL(v2,v1) THEN

        result := v1;

    ELSE

        result := v2;

    END IF;

END;

But when I use it within an INSERT statement it gives strange errors such as the following:

Could not execute 'insert into temp ( amount) select min_value(3,4) from dummy' in 3 ms 626 µs .

SAP DBTech JDBC: [478]: user defined function runtime error: exception: CompilationFailedException: No details

Program :

Error: Unexpected end of input

Here is a very simplified example that gives the above error (using SP6)

create table temp (

  amount decimal(8,3)

);

insert into temp (amount)

select min_value(1, 2)  from dummy;

The 'select' on its own works fine, but the insert gives the error.

Am I doing something obvious wrong?

David

[Former Member]

Hi

I am OK with the Revison 91, How about the Revision of the HANA you are using?

Best Regards

[Former Member]

Hello David

I could do this with the Revision 74. I hope can be useful for you.

Thanks and regards.

[jmsrpp]

FYI ... the steps you provided return the same exception in SPS07 (Revision 70):

Could not execute 'insert into temp (amount) select min_value(1, 2) from dummy' in 512 ms 6 µs .

SAP DBTech JDBC: [478]: user defined function runtime error: exception: CompilationFailedException: No details

Program :

Error: Unexpected end of input

The sub-query also works if you remove the function from it and simply select a decimal value from another table.

I didn't see anything interesting in the log files either unfortunately.

Jim