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,
language sqlscript reads sql data as
result_add = :x + :y;
result_mul = :x * :y;
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"
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