Question Re: Scalar UDF with several returning parameters in Technology Q&A

Scalar UDF with several returning parameters

felix_guldner — Thu, 26 Nov 2015 08:43:18 GMT

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

Re: Scalar UDF with several returning parameters

pfefferf — Thu, 26 Nov 2015 12:45:05 GMT

Hello Felix,

what calculations are you doing in the scalar UDF? Are you just doing (a+b) and (a*b) (what could be replaced by expressions) or something more sophisticated?

To my knowledge it is not possible to return both return values within one call in a select statement.

Best Regards,

Florian

Re: Scalar UDF with several returning parameters

felix_guldner — Thu, 26 Nov 2015 12:54:26 GMT

Hello Florian,

we are doing something more sophisticated. Actually, within the scalar UDF we are calling a complex stored procedure which selects data from several different tables/views and calculates some numbers in the end. These numbers are then returned to the UDF and from there just passed through.

Our overall requirement is to enrich a table/view with some calculated fields. These fields require a quite complex calculation, which is performed in a stored procedure.

The coding above was just a very simple example.

Best regards

Felix

Re: Scalar UDF with several returning parameters

nithinu — Fri, 27 Nov 2015 05:27:35 GMT

Hi Felix,

Is it possible for you to use a Table UDF to return multiple parameters ? Since the function will always return a single row, you can use the 'cross join' to connect your Table/View with the UDF. This is just a suggestion, I could be wrong!

Regards,

Nithin

Re: Scalar UDF with several returning parameters

nithinu — Fri, 27 Nov 2015 05:48:05 GMT

I think my above suggestion will not work since the HANA UDF will not support the Input parameter as fields from other tables. You may have a look on the following discussion field or table alias is not allowed as an input... | SCN .

Regards,

Nithin

Re: Scalar UDF with several returning parameters

felix_guldner — Fri, 27 Nov 2015 06:50:57 GMT

Hi Nithin,

it would have been a good workaround, but as you already mentioned, it's not possible to use table fields as input parameters for the table UDF. I just verified that in a simple example.

Best regards

Felix