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;

[whereistejas · Accepted solution]

abhishek.hazra thank you for pointing out the blog. I had already gone through it before posting the question but found the method too cumbersome. I was looking for a simpler solution, sadly, that was not to be. I was able to find an answer to this question. The approach suggested in the above blog is correct. But, there is a simpler way to implement the same, as follows:

FUNCTION "db::operate_numbers_func"(
	IN IV_VAR1 NVARCHAR(5000),
	IN IV_VAR2 NVARCHAR(5000),
	IN IV_OPERATIONS NVARCHAR(5000)
	-- IN IT_NUMBERS TABLE (VAR1 INT, VAR2 INT),
	-- IN IT_OPERATIONS TABLE ( OPERATION NVARCHAR(1) )
)
	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);

	DECLARE it_numbers1 TABLE (var1 INT);
	DECLARE it_numbers2 TABLE (var2 INT);
	DECLARE it_operations TABLE ( operation NVARCHAR(1) );
	
	it_numbers1 = SELECT a.result "VAR1" FROM SQLSCRIPT_STRING:SPLIT_TO_TABLE(:iv_var1,',') AS a;
	it_numbers2 = SELECT a.result "VAR2" FROM SQLSCRIPT_STRING:SPLIT_TO_TABLE(:iv_var2,',') AS a;
	it_operations = SELECT TRIM(BOTH '''' FROM a.result) "OPERATION" 
						FROM SQLSCRIPT_STRING:SPLIT_TO_TABLE(:iv_operations,',') AS a;


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

I'm using a built-in SQLScript library called `SQLSCRIPT_STRING`. The details can be found in the sqlscript reference.

I hope this helps whoever comes here looking for the answer.

[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.