on 11-26-2015 8:43 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
93 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.