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.