Table Type input Paramter in sAP Caculation View

[former_member293511]

Hi Experts,

I can able to create input as a table type in procedure as like below.

Sometimes users will send input as like above to views/procedure.

I want to achieve the same in calculation view/scripted calculation view.

As per my understanding, we can pass only scalar input parameter in views and multiple entries wont be in table type format.

Kindly help me on this.

Thanks,

Rajavelu

[pfefferf]

No this is not possible for calculation views (graphical and scripted ones).

What you would wanna achieve with that?

[former_member293511]

Thanks for reply!

End user will send set of article ID,Price_Zone ID & UOM combinations as input.

Based on that combination , we want to filter data from tables with some complex logics and this was achieved in procedures earlier but client prefer views now.

If I send scalar input through views it will bring huge amount of data and that will directly impact performance.

Regards,

Raja

[pfefferf]

How does your client wanna call the functionality? Depending on that there were different options, e.g. creating a calc. view which can be called for one parameter combination. With that for each combination the view can be evaluated and the combined result returned.

[former_member293511]

From Microstrategy tool.

I heard we can do the same with table functions but not sure.

[pfefferf]

Yes, to table functions you can pass table typed parameters. But I do not know the tool you mentioned and what options it offers to integrate HANA.

[former_member293511]

florian.pfeffer could you please share syntax of table functions with table type as input parameter or example?

[pfefferf]

Syntax can be found in the official documentation (for instance for pure SQLScript check the documentation here; for runtime declarations in XSC or XSA please check the corresponding developer guides).

Litte example in SQL console to create such a function (set schema/name according to your needs/requirements):

create function "MISC"."MISC_TFUNC_TPAR"(in t_param table(id integer, value nvarchar(255)))
returns table(id integer, value nvarchar(255))
language sqlscript
sql security invoker
as
begin
    return select * from :t_param;
end;

Calling it in an anonymous block in a SQL console:

do
begin
    lt_param_data = select 1 as id, 'Test 1' as value from dummy 
                    union select 2 as id, 'Test 2' as value from dummy;
                    
    lt_func_res = select * from "MISC"."MISC_TFUNC_TPAR"(:lt_param_data); 
    
    select * from :lt_func_res;
end;

Regards,
Florian

[former_member293511]

@florian

I created below table function.

And used same in calculation view but Dono the next steps to pass input from views.

Thanks,

[pfefferf]

Not sure what you wanna do. You can use table functions with table type input parameters in calculation views in newer HANA versions, but it is still not possible to create a table type calc. view parameter and pass that to the table function.