Skip to Content
0
Jan 24, 2014 at 01:05 PM

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

306 Views

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