Skip to Content
0
Nov 26, 2015 at 08:43 AM

Scalar UDF with several returning parameters

595 Views

Hi,

I have a scalar user defined function with one (or more) input and two (or more) returning parameters. I want to use that function via declarative logic in a select statement. Both returning parameters shall be used, but since the function call is quite expensive, it should only be called once.

At the moment I haven't found a solution how to use both returning parameters with only one call. Here is a simple executable example (which is actually coming from the SQLScript Guide):

create function func_add_mul(x double, y double)
  returns result_add double,
          result_mul double
  language sqlscript reads sql data as
begin
  result_add = :x + :y;
  result_mul = :x * :y;
end;


do
begin
  lt_data = select 1 as a, 2 as b from dummy
      union select 3 as a, 4 as b from dummy;
        
  select a, b,
        func_add_mul(a, b).result_add as "add",
        func_add_mul(a, b).result_mul as "mult"
  from :lt_data;
end;


drop function func_add_mul;

When the select statement in line 14 is executed, the UDF func_add_mul is called twice for every row of the table. This is quite inefficient, since the UDF returns both results in a single call. I'm looking for a way (or a workaround) to achieve the same thing with only ONE call for every row.

The only workaround I can think of would be to use imperative logic. Then I would first build two arrays containing the values a and b and then call the UDF once for every combination. Then I could unnest the resulting arrays into a table again. But I think due to performance reasons it would be better to use declarative logic...

Does anyone know a solution for this problem? Or is it a current limitation for scalar UDFs? I'm using HANA SPS10 by the way.

Thanks and best regards

Felix