mapping calculation view parameters to table function with tabular input

[whereistejas]

Hi,

I have a table function with input parameters like:

FUNCTION "db::operate_numbers_func"(
	IN IT_NUMBERS TABLE (VAR1 INT, VAR2 INT),
	IN IT_OPERATIONS TABLE ( OPERATION NVARCHAR(1) ) -- "A"DD, "S"UBTRACT, "D"IVIDE, "M"ULTIPLY
)
	RETURNS TABLE (ANS INT)
	LANGUAGE SQLSCRIPT 
	SQL SECURITY INVOKER AS 
BEGIN 

I wish to expose this table function through a calculation view as an OData service.

I created input parameters as such in the calculation view, all input parameters have the "multiple entries" field checked to true.

Can someone help me figure out how to map the input parameters of the calculation view to input parameters of the table function?

The entire table function is as follows:

FUNCTION "db::operate_numbers_func"(
	IN IT_NUMBERS TABLE (VAR1 INT, VAR2 INT),
	IN IT_OPERATIONS TABLE ( OPERATION NVARCHAR(1) ) -- "A"DD, "S"UBTRACT, "D"IVIDE, "M"ULTIPLY
)
	RETURNS TABLE (ANS INT)
	LANGUAGE SQLSCRIPT 
	SQL SECURITY INVOKER AS 
BEGIN 
	DECLARE idx, lv_a, lv_b, lv_c INT;
	DECLARE lt_numbers TABLE (var1 INT, var2 INT);
	DECLARE et_result TABLE (ans INT);


	IF ( RECORD_COUNT(:it_numbers) = RECORD_COUNT(:it_operations) ) THEN
		FOR idx IN 1..RECORD_COUNT(:it_numbers) DO
			IF :it_operations.operation[:idx] = 'A' THEN
				et_result.ans[:idx] = :it_numbers.var1[:idx] + :it_numbers.var2[:idx];
			ELSEIF :it_operations.operation[:idx] = 'S' THEN
				et_result.ans[:idx] = :it_numbers.var1[:idx] - :it_numbers.var2[:idx];
			ELSEIF :it_operations.operation[:idx] = 'M' THEN
				et_result.ans[:idx] = :it_numbers.var1[:idx] * :it_numbers.var2[:idx];
			ELSEIF :it_operations.operation[:idx] = 'D' THEN
				et_result.ans[:idx] = :it_numbers.var1[:idx] / :it_numbers.var2[:idx];
			END IF;
		END FOR;
	END IF;
	
	RETURN
	SELECT *
	FROM :et_result;
END;

[Abhishek_Hazra]

Hi,

There are already available blogs regarding this. You can't directly assign multi entry input parameter to table functions. You need to create a dynamic filter definition for it & consider the performance penalties due to that.

Please refer to : https://blogs.sap.com/2019/01/17/passing-multi-value-input-parameter-from-calculation-view-to-table-...

Best Regards,
Abhi

[Abhishek_Hazra]

That’s really nice of you to share, good to know, however I have tried the solution mentioned in the blog, but in a complex calculation scenario it slows down the performance(most probably due to the dynamic sql usage).
There is another way though, a custom stored procedure/function to gather the passed input parameters & transpose as table entries & later feeding that in to the table function as input parameter, however, that did not improve the performance for complex scenarios in my case either. We have managed to redo the solution with only graphical components & with apt hints managed to get better performance than the table function with multi input parameters.

Best Regards,

Abhishek

[whereistejas]

You can convert the stored procedure to a table function and then use the table function in a calculation view. When you expose the calc view as an xsodata service, it manages all the input parameters (multiple or single) for you. However, this is useful only if you are going to convert the calc view into a odata service.