Skip to Content
Technical Articles
Author's profile photo Konrad Zaleski

Passing multi-value input parameter from Calculation View to Table Function in SAP HANA – step by step guide

Introduction

In my previous post I demonstrated how to create Table Functions (TF) in SAP HANA XS Classic. One of the TF disadvantage, which I mentioned there was the fact, that they are not supporting multi-value input parameters – and this is official:

Personally I think this is quite a big limitation, because when some functionality cannot be covered by graphical view, then recommendation is to go for SQL Table Function – so why we cannot pass multi-value parameter there? In the reporting world this is one of the most basic functionality to give the user flexibility in defining input criteria, isn’t it?

For some scenarios this limitation forced me to implement complex logic in graphical views, although in SQL it could be done much easier. But I could not take away from the end user possibility of selecting more than one value.

For small datasets when there was no option for implementing report logic graphically, I was creating Table Function (without input parameters) and then in graphical view, which consumed that TF I was creating variables. But as you might know in this approach, values provided by the user in variable  were not pushed down to the table directly, so performance of such a solution is simply bad.

Finally I got a requirement which forced me to find a way of passing multi-value parameters directly to the SQL query – this was for creating BOM (Bill-of-Material) report, where user wanted to display for inputed materials all its components. Now imagine running such a report for all existing materials and recursively searching for all its components from all levels – impossible even for HANA 😉 This was the moment when I started deeply look for the solution of passing multiple values through Calculation View Input Parameter to Table Function.

In this blog I will share my findings and workarounds which I discovered.

Why passing multiple values from Calculation View parameter to underlying Table Function parameter doesn’t work?

Common developers mistake is trying to apply filter combining multi-value input parameter with SQL IN predicate, which won’t work:

To visualize the issue here let’s create a simple Table Function, which will return only the value of inserted parameter:

FUNCTION "_SYS_BIC"."TMP::TF_DUMMY" ( INPUT VARCHAR(100) ) 
	RETURNS TABLE
		(
			INPUT_PARAMETER VARCHAR(100)
		)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS

BEGIN

	SELECT :INPUT AS "INPUT_PARAMETER" FROM DUMMY;

END;

Then let’s create calculation view which will consume that TF (1). Before activation remember to map input parameter between TF and calculation view (2) and set the input parameter as Multiple Entries:

After activation pass multiple values to the selection screen and check the output:

At first glance the result may seem to be correct, because these are the values which we wanted to pass to the IN predicate, but in fact it’s wrong. Here instead of passing 3 separate values, we are passing single string with all three values concatenated. This is how the syntax in background would look like:

WHERE COLUMN IN :MULTI_VALUE_IP => WHERE COLUMN IN ‘ AA, BB, CC ‘

So instead of filtering three separate values there is a filter on one concatenated value (I skipped here single quotes to make it more clear, because actual value which is passed would be: ‘ ”AA”,”BB”,”CC” ‘ )

Below I showed how HANA converts multiple inserted values (on the left) and what values we would need for IN predicate to make it work (on the right):

What are the workarounds to pass multi-value input parameter to Table Function?

There are two approaches to implement multi-value parameter handling.

  1. Using APPLY_FILTER function
  2. Creating custom function splitting string into multiple values

For the demonstration purposes I created database table TEST_ORDERS and inserted some sample records there.
The structure of the table is as follows:

I will use it as a data source for further demonstrations.

1. Using APPLY_FILTER() statement

The APPLY_FILTER is SQL function, which allows creating dynamic filter on a table. Syntax of the function is following:

APPLY_FILTER(<table_or_table_variable>, <filter_variable_name>);

To give you more examples I prepared three different scenarios.

Scenario 1.

Requirement is to create Orders report with Mandatory and Multi-Value Input parameter for Customer Number column.

Here is definition of the Table Function:

I. Define input parameter. Assign varchar data type to the input parameter (even if the filtered column is of integer type). Consider that HANA concatenates multiple inserted values into single string, so to allow user to input as many values as it’s supported I defined maximum number of characters which is 5000. I will describe this limitation in details in the last section.

II. Create a variable and assign dynamic filter definition. Filter definition syntax should be the same as it is used after the SQL WHERE clause. Combine the filter definition with the input parameter ( || symbol is used for concatenating strings )

III. Assign SELECT query to the variable. This Table Variable will be consumed by APPLY_FILTER function.

IV. Use APPLY_FILTER function. As first function parameter provide Table Variable with SELECT query (here :ORDERS) and for second parameter – Variable with dynamic filter definition (here :FILTER). Assign output of APPLY_FILTER to another Table Variable (here FILTERED_ORDERS)

V. Query the Table Variable with the output of APPLY_FILTER function.

Once the table function is activated create a graphical Calculation View. As a source provide created Table Function and map input parameters:

Select options Multiple Entries and Is Mandatory for that input parameter:

Now let’s run the data preview, select multiple values on selection screen and see the output:

So we just passed multiple values through Calculation View to Table Function!

Scenario 2.

Requirement is to create Orders report with Optional and Multi-Value Input parameter for Customer Number column.

To support optional multi value parameter there is a need to adjust a part of the code (only part II, everything else keep as in Scenario 1.):

II. Create a variable and assign dynamic filter definition. Here there is additional check implemented. If the value of inserted parameter is empty then assign to FILTER variable string 1=1, otherwise use the same condition as in first scenario. When you pass to APPLY_FILTER function value of 1=1 as a filter variable, then all records will be returned (because this condition is always valid). This is a workaround for displaying all the records when user doesn’t provide any value to input parameter.

Now when you don’t provide any value in input parameter, you will get all records in the output:

For other inserted values view will act as in Scenario 1.

Scenario 3.

Requirement is to create Orders report with Two Mandatory and Multi-Value Input parameters one for Customer Number and second for Category column.

In this scenario two parts of code need small adjustments compared to the Scenario 1.

I. Add new input parameter for Category.

II. Adjust string assigned to FILTER variable to include CATEGORY as a filter.

In Calculation View you need to add new input parameter and map it with the one which was created in Table Function (IP_CATEGORY):

Now you can input multiple values for both parameters and filter will consider both of them:

2. Creating custom function splitting string into multiple values

Second approach to enable passing multiple values to Table Function is to create custom SQL function. Logic inside that function will split concatenated strings into multiple records. Here is the code for the custom function:

FUNCTION "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000) ) 
	RETURNS TABLE
	(
		"OUTPUT_SPLIT" VARCHAR(5000)
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN

	DECLARE COUNTER INT := 1;
	SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY;	
	SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;
	
	WHILE( LENGTH(:INPUT_STRING) > 0 )	
	DO
	
	   SPLIT_VALUES =	
	   
	   		SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY	 
				UNION 	   
			SELECT SINGLE_VAL FROM :SPLIT_VALUES;
	
	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;
	   	
	END WHILE;
	
	RETURN
	
	SELECT REPLACE(SINGLE_VAL,'''','') AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 

END

Now we can consume that function in the SQL query. For the demonstration purposes let’s use Scenario 1.

Scenario 1.

Requirement is to create Orders report with Mandatory and Multi-Value Input parameter for Customer Number column.

Create graphical Calculation View, use Table Function as a source and map input parameters:

Now let’s run the data preview, select multiple values on selection screen and see the output:

The result is the same as in the approach with APPLY_FILTER function. From the performance perspective APPLY_FILTER will work faster, but the difference will be minor.

Limitations

Although the presented solutions allow to pass multiple values through Calculation View to Table Function, there is a major limitation for both approaches implementations.

As mentioned at the beginning of that post HANA concatenates all inserted values into single string. Considering the fact that maximum number of characters allowed in VARCHAR data type is 5000, at some point you may realize that number of inserted values is limited.

Let’s take an example of Material (MATNR) field which in SAP is of VARCHAR(18) data type. When inserting two materials:

Concatenated string will look as follows:

The concatenated string length for this two values is:

  • 18 x 2 characters for two materials (36 in total)
  • 2 x 2 single quotes (4 in total)
  • 1 x comma (1 in total)

For the presented example the total string length is 41.

Keeping in mind that the maximum varchar length is 5000, for this scenario the maximum number of Materials which you can pass to table function equals to:
5000 / (18 characters + 2 quotes + 1 comma for each material) ~ 238.

For some scenarios it can be more than enough, but sometimes you want to allow import to the input parameter thousands of values. In such a case if the string for input parameter will exceed the limit of 5000 characters you will get error as below:

Summary

The workaround shown in my blog allows you to pass multiple values into Table Function however you need to keep in mind that there is a big limitation for the number of values which you can pass. Also when the report contains many input parameters and some of them are mandatory, others optional then it can be quite a big effort to support all the various scenarios and then maintenance can be difficult.

My approach for reporting on Table Function is to pass all mandatory input parameters into the SQL code. For optional selection criteria I create Variables directly in graphical Calculation View.

From version of SAP HANA 2.0 SP03 (XS Advanced) there is a new functionality, which can be applied for described scenarios – see documentation here (thanks Lars Breddemann  for sharing!)

There are many different reporting scenarios, so I will be really appreciated if you comment the post, share your ideas on that topic or provide your feedback.

 

Thanks for reading!

Likes and shares always very welcomed 🙂

Assigned Tags

      59 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo RAJJAN KHAN
      RAJJAN KHAN

      hey really very useful content

      Author's profile photo Alexander Zlobin
      Alexander Zlobin

      Thanks a lot! very useful!

      Author's profile photo Dorothy Eiserman
      Dorothy Eiserman

       

      Great blog!

      Author's profile photo Supharoekrat Chonlanotr
      Supharoekrat Chonlanotr

      Great blog. Can be use in BW/4HANA ?

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Sure, you can use this approach in your native developments in BW/4HANA.

      Author's profile photo Shyamala Valeti
      Shyamala Valeti

      Well explained ...Nice blog...

       

      Thanks

      Shyamala

      Author's profile photo Satish V
      Satish V

      Nice one Konrad...easy to follow ...Great ..Thank you..

      Author's profile photo Ravi Madari
      Ravi Madari

      Hello Konrad,

       

      Thanks for the wonderful post.

       

      I’ve a sql scripted calculation view and I want to allow multiple values in an input parameter. Need to be able to filter the script view based upon multiple values in one parameter. Can you guide me with this scenario please.

       

       

      Thanks

      Ravi.

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      APPLY_FILTER function should also work in Scripted View, however if possible you can migrate scripted view to table function first and then apply the approach which I described in the post.

      Author's profile photo Andreas Dietz
      Andreas Dietz

      Dear Konrad,

      although the table Integration and interface capabilities have been extended with the rise of SAP HANA 2.0 SP03 we have to consider the real world and the prerequisites:

      • not all applications run on that SP level and migrating to it might take some time here and there
      • not all systems and customers haven't adapted to the web IDE as yet

      That is, as users require selctions of multiple values, your approach will remain a good one  to overcome the limitations for quite a while even as we have to accept further restrictions as you pointed out.

      Your contribution is of high value because it shows a deep insight of how HANA works at that point in conjunction with a common solution to get the things going (later rework accpeted!)

      Besides: It's sometimes hard to find the SAP standard documentation 🙂

      Thank you very much for your high quality post!

      @bi4

       

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Thanks Andreas! Nice to hear that 🙂

      Author's profile photo Rajavelu Angalan
      Rajavelu Angalan

      Konrad Załęski  Can you please look into below link?

      https://answers.sap.com/questions/644789/table-type-input-paramter-in-sap-caculation-view.html

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      As Florian replied - table type for input parameter is not supported for graphical and scripted views. If you are using HANA 2.0 XSA you can check new option for passing multiple values to table function.

      Author's profile photo David Courtens
      David Courtens

      This just saved my day, thanks!

      Author's profile photo Tapan-Kumar Pradhan
      Tapan-Kumar Pradhan

      Hi Konrad Załęski,

      Thanks for the nice blog! It's really helpful. I am currently struggling with the restriction of 5000 characters length of the input parameter. How does multi value input parameter behave in case of hierarchy nodes selected in the prompt? Basically I have a multi value input parameter in calculation view and it's tied to an infoobject in BW and restricted globally in BEx query with a hierarchy node variable. If user selects the top node of the hierarchy, query fails with the message "String too long". I think system internally creates a string with leaf nodes of the top node selected by user which in turn becomes more than 5000 character and it fails. I must pass the input parameter values to table function as the overall calculation is dependent on the number of values selected by user. Therefore, restriction only at query level and not using the values in table function would not help.  Do you think if there is any solution for this? Thank you for your time!

       

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      I'm not sure how does it work with bex, but when using Hierarchy in native calculation view, then from my observation it anyway takes the last level of the hierarchy and passes it to the query. In this case hierarchy is used only for the display, but when selecting multiple values it behaves same way as regular input parameter. Make sure that Input Parameter is set as VARCHAR(5000) both in TF and in CV. Also consider that when generating the string additionally quotes and commas are being added which additionally limits the string length. Maybe you are trying to pass too many values at once.

      Author's profile photo Rakesh Manchala
      Rakesh Manchala

      Hello Konrad,

      Thanks for the post. I have one question.

      I have developed one Table Function (TF_TEST_ABC)  and one Graphical Calculation View (CV_TEST_ABC)

      In CV_TEST_ABC i have one Mandatory and Multi-Value Input parameter.

      i have to consume CV_TEST_ABC  in TF_TEST_ABC. Is there any way to achieve it using place holder.

      when i am trying the following syntax it is not working, the following syntax works only in case of single value input parameter.

      Select * FROM “_SYS_”.”workshop.test2/CV_TEST_ABC” (PLACEHOLDER.”$$InputParam1$$” =>  :userId,

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Syntax which you mentioned should also work for multiple values. Make sure that you pass values in proper format - each value should be surrounded by double quotes. In your example if you have three users: AA, BB, CC then you should pass following string:

      DECLARE USER_ID VARCHAR(5000) := '''AA'',''BB'',''CC''' ;
      Author's profile photo Booma A
      Booma A

      Hello Konrad,

      Excellent Article. It gave much insights to people like me who have just started working on it. I have a question. I will have 50,000+ records of data which i would like to left join with other tables  and arrive at other field values based on conditions. If Table function doesnt support multiple input data(Work around also has limitations) what would be the other best approaches for handling 50,000+ records criteria?

       

      Thank you.

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      You can try to create table function without any input parameters. Then use that table function in calculation view and create Variable instead of Input Parameter in calculation view directly (variables supports multiple input values). Disadvantage of that solution is that filter from variable will not be pushed down to table level, so table function will execute query for all resultset and on top of that variable will filter records - this means that the performance of will be very low.

      Author's profile photo prem kumar
      prem kumar

      Hello Konrad,

      Nice article. One question though. I read another post(couldnt find it again) where they have stated with examples from visualization plan that the variables passed are indeed used in the WHERE clause in the bottom most node but based on your above answer it looks like the variable can only filter from the result set. Can you clarify please?

      Also, one additional question - Is table user defined function & table function both the same?

      Thank you.

       

       

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Hi Prem,

      This is true that variables are passed to the lowest node, but only when talking about graphical calculation view (and also not in all cases - for some complex data models sometimes there might be some issues). When using Table Function as a data source of calculation view, then variables will not be passed to the table level of SQL query.

      Table Function is just an abbreviation of Table User-Defined Function.

      I hope it's clear.

      Author's profile photo prem kumar
      prem kumar

      Thank you Konrad.

      Author's profile photo Dhinakaran E
      Dhinakaran E

      can we pass multi values in variable?

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Sure, this is pretty straightforward. Just tick "Multiple Entries" checkbox on variable properties and then you will be able to pass multiple values.

      Author's profile photo Dhinakaran E
      Dhinakaran E

      single string can we pass multiple values concatenated using variables or input parameter?

      example:

      VARIABLE =  '"A","B","C"'

      INPUT  PARAMETER =  '"A","B","C"'

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      I don't see any reason, why to pass multiple values this way. However If this is required you can use input parameter and then split inputted string into multiple lines and apply it in WHERE condition of SQL query (in other blog post you can check how to split string into multiple lines)

      Author's profile photo Satish V
      Satish V

      Excellent Blog Konrad...it will be very helpful as the requirement for passing multiple values is quite usual in the real-time world..

      Thanks for your time and effort to explain in such a nice way ....

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Thanks!.

      Author's profile photo Platini Keto Makambo
      Platini Keto Makambo

      excellent content. Well done

      Author's profile photo Sarada Sarvepalli
      Sarada Sarvepalli

      Very nice blog Konrad. The above worked fine with in SAP. But our users use the Power BI tool to access these calculation views. And when we are selecting multiple entries in there, it isn't fetching the results. Can you please help me on this?

      Thanks,

      Sarada

      Author's profile photo Venkat Koppuravuri
      Venkat Koppuravuri

      Nice blog Konrad. Does this work even if we give single value as an input parameter?

      Author's profile photo Praveen Kulkarni
      Praveen Kulkarni

      Hello,

      I need to use the table function in select clause so that i can pass in parameters from a join and i get below error:

      SAP DBTech JDBC: [7]: feature not supported: field or table alias is not allowed as an input of table functions

      Is there a way to do this yet? Sample query would look like

      select tab.col1, (select * from table_function(tab.col1,tab.col2)) y

      from table_in_my_app tab

      ;

      And, the function has to be table function due to some unavoidable reasons. Please help.

      Thanks!

       

      Author's profile photo Shubham Jain
      Shubham Jain

      Hi Konrad,

       

      I tried to follow steps as mentioned in your post, and is working fine when i am passing numeric values with multiple values at same time.

      But when i try to pass string as input parameter, i am getting error as below:

      SELECT * FROM "SYSTEM"."shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR"('STEVE');

      Could not execute 'SELECT * FROM ...' in 367 ms 906 µs .
      SAP DBTech JDBC: [260]: invalid column name: search table error: "SYSTEM"."shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR": line 12 col 1 (at pos 489): [260] (range 3) invalid column name exception: invalid column name: STEVE: line 2 col 125 (at pos 515) (please check lines: 12, 15, 19, 20)

       

      Can you please suggest any workaround in order to solve this?

       

      Regards,

      Shubham Jain

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      If you query it from SQL console you need to add additional quotes to the string, for example to input two values (STEVE and TEST) use:

      SELECT * FROM “SYSTEM”.”shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR”('''STEVE'',''TEST''');

       

      Regards

      Author's profile photo Shubham Jain
      Shubham Jain

      Thank you Konrad !! It worked.

      Author's profile photo Prem Shanker
      Prem Shanker

      Very Nice Blog.

      Thank you Konrad

      Author's profile photo Gaurav Kumar
      Gaurav Kumar

      Hi,

      Thanks for this detailed article. How about passing multiple values from CV to Procedure to CV back(CV->PROC->CV). I have one input parameter called IP_PERIOD which is like 05.2020(MM.YYYY) format and I also have one more input parameter IP_YEAR which needs to be feed from IP_PERIOD. Basically the year(YYYY) portion of the period should be passed into IP_YEAR. It can have multiple values also such as 04.2020, 05.2019. Thats why I have created a stored proc which will accept input as IP_YEAR('''04.2020'',''05.2019''') and will pass output as unique year - 2019 and 2020. Now issue is my IP_YEAR in my CV after doing mappings with proc has to be multiple values. Even if I pass like '''2019'',''2020''' as input to IP_YEAR, it does not accept it.

      Any help.

      Thanks,

      Gaurav

      Author's profile photo Hammad Hassan Shah
      Hammad Hassan Shah

      Hello,

      Konrad, Nice Blog. My requirement is to Fetch range of data. Means, How can I Use Range of Input Parameters in HANA Graphical Calculation Views. E.g. I want data based on Fiscal year If I pass 2018 to 2020 in the input parameters. How can I do this?

      Thanks.

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Hi Hammad,

      You need to create two separate input parameters (i.e "IP_DATE_FROM" and "IP_DATE_TO")

      Author's profile photo Hammad Hassan Shah
      Hammad Hassan Shah

      Hi Konrad,

      Thanks.

      I got your point. But in This case I just get the data of only 2 years/Dates data but I want the range of data. That is in between of dates that I put in input parameters. E.g. I put 2018 in "IP_YEAR_FROM" and 2020 in "IP_YEAR_TO". Now the point is, I want the data of 2018, 2019, and 2020. How can I do this?

      Thanks,

      Hammad Hassan Shah.

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      You need to apply date range filter in your SQL WHERE clause:

      WHERE "YEAR_COLUMN" BETWEEN :IP_YEAR_FROM AND :IP_YEAR_TO
      Author's profile photo Hammad Hassan Shah
      Hammad Hassan Shah

      Thanks Konrad.

      Author's profile photo Cristian Cafure
      Cristian Cafure

      Hi Konrad Załęski

      We are trying to count the number of records in a HANA view based on the date filter coming from a Worklist (SAP Tax Compliance). So the user chooses a date range via SAP Tax Compliance / Fiori and those values, we want to move to a HANA view. Now, neither a Table Function (SELECT COUNT) nor an aggregation are bring the results we want. Or a way to pass the date filter from Fiori maybe via an input parameter to the HANA view...

      Any ideas?

      Example: HANA view = total records 400 | HANA view for x to y dates = total records 250. Currently, the system always shows 400.

       

      Cristian

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Hi Cristian,

      I suggest to open the question on the SAP community and share the code. Without this detailed information it's hard to guess what can be the root cause.

      Regards

      Author's profile photo Cristian Cafure
      Cristian Cafure

      Hi Konrad Załęski,

      The code is simple:

      SELECT
      COUNT (*) as "amt_items_prewhite",
      COUNT (DISTINCT "USNAM") as "amt_users_prewhite"
      FROM "_SYS_BIC"...';

      The problem here is that the user inputs a data range in a tool called Tax Compliance (Fiori). And such value, I'm not aware of how to take it from the Frontend to HANA.

      Is there a way that you are aware of to bring a value that the user writes manually?

       

      Regards,

      Cristian

      Author's profile photo Abhishek Hazra
      Abhishek Hazra

      Hello Konard,

      Kudos for such a nice blog, surely it will save many from braintwisting ideas. However, I have a comment for the second approach where you split the input parameter string into multiple string values. It looks like if a user decides to pass just one value in the multivalue parameter scope (quite possible to do so in real cases), the function : "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000) )  won't return anything, since there is no SUBSTR_BEFORE validation for this case (without a comma(,) symbol in the string).

      For example : if we try select output_split from "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( '''single_val''' )  - it should have returned 'single_val' as output, but it won't return that.

      Feel free to correct me if I am wrong.

      Best Regards,
      Abhi

      Author's profile photo Konrad Zaleski
      Konrad Zaleski
      Blog Post Author

      Hi Abhishek,

      Yes - you are right. If single values need to be supported then in addition, there should be a logic for example adding trailing commas if there is single value inputted.
      Anyway APLLY_FILTER is a better approach.

      Regards,

      Konrad

      Author's profile photo Abhishek Hazra
      Abhishek Hazra

      Yes, the APPLY_FILTER one is a gem 🙂

      Regards,
      Abhi

      Author's profile photo Luc VANROBAYS
      Luc VANROBAYS

      Hello,

      Excellent Blog really! In order for the TF_SPLIT_STRING to work when single value is selected in a Multiple Entries Input Parameters, I slightly modified the function as following:

       

      FUNCTION "SAPABAP1"."Interparking.Views::TF_SPLIT_STRING"
      ( INPUT_STRING VARCHAR(5000) ) 
      	RETURNS TABLE
      	(
      		"OUTPUT_SPLIT" VARCHAR(5000)
      	)
      	LANGUAGE SQLSCRIPT
      	SQL SECURITY INVOKER AS
      BEGIN
      
      	DECLARE COUNTER INT := 1;
      /* begin: modified in case single value is used in a multiple entries IP
      	SPLIT_VALUES = SELECT case SUBSTR_BEFORE(:INPUT_STRING,',')  
      	when ''
      	then :INPUT_STRING
      	else
      	SUBSTR_BEFORE(:INPUT_STRING,',')
      	end AS SINGLE_VAL FROM DUMMY;	
      /* end: modified in case single value is used in a multiple entries IP
      	SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;
      	
      	WHILE( LENGTH(:INPUT_STRING) > 0 )	
      	DO
      	
      	   SPLIT_VALUES =	
         
      	   SELECT SUBSTR_BEFORE(:INPUT_STRING,',') AS SINGLE_VAL 
                 FROM DUMMY 
      /* begin: added in case single value is used in a multiple entries IP	
                 WHERE SUBSTR_BEFORE(:INPUT_STRING,',') <> '' 
      /* end: added in case single value is used in a multiple entries IP
      	UNION 	   
      	   SELECT SINGLE_VAL FROM :SPLIT_VALUES;
      	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;   	
      	END WHILE;
      
      	RETURN
      	SELECT REPLACE(SINGLE_VAL,'''','') AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES ORDER BY 1; 
      /* in SQL Console
      	SELECT * FROM "SCHEMA_NAME"."<Package>::TF_SPLIT_STRING"('AA,BB,CC');
      */
      
      END;

       

      Author's profile photo Jonathan Haun
      Jonathan Haun

      Slight modification to the the TF_SPLIT_STRING will fix the single value problem..

      	RETURNS TABLE
      		(
      		"OUTPUT_SPLIT" VARCHAR(5000)
      		)
      	LANGUAGE SQLSCRIPT
      	SQL SECURITY INVOKER AS
      BEGIN
      	DECLARE COUNTER INT := 1;
      	DECLARE COMMA_COUNTER INT := 0;
      	
      	SELECT LOCATE(:INPUT_STRING,',') INTO COMMA_COUNTER FROM DUMMY;
      	
      	IF COMMA_COUNTER > 0
      		THEN
      			SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,',') as SINGLE_VAL FROM DUMMY;	
      			SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;
      		ELSE
      			SPLIT_VALUES = SELECT :INPUT_STRING as SINGLE_VAL FROM DUMMY;
      		END IF;
      	
      	WHILE( LENGTH(:INPUT_STRING) > 0 )	
      	DO
      	
      	   SPLIT_VALUES =	
      	   
      	   		SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY	 
      				UNION 	   
      			SELECT SINGLE_VAL FROM :SPLIT_VALUES;
      	
      	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;
      	   	
      	END WHILE;
      	
      	RETURN
      	
      	SELECT REPLACE(SINGLE_VAL,'''','') AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES WHERE SINGLE_VAL <> ''; 
      
      END
      Author's profile photo Pooja Patil
      Pooja Patil

      Hi Konrad,

      Great blog!
      Could you please also help with explaining how to pass multiple values of a input parameter from target view(graphical calculation view to value help view(graphical) as
      I am facing issue with passing multiple values of input parameter from main graphical calculation view to graphical calculation views created for value help. The same input parameters work fine when they are set for single values.
      I posted this question (below link) but nobody replied. Could you please help if there is any solution to the issue:
      https://answers.sap.com/questions/13387781/issue-with-mapping-multiple-entries-input-paramete.html

      Author's profile photo Samuel Muñoz
      Samuel Muñoz

      Hello Konrad Zaleski, the article is good, however I want to clarify something, between the 2 approaches (APLLY_FILTER and Function) it will be much better to use the function since in this way you will indicate to the SQL optimizer which should be the clache plan it should generate.

      Author's profile photo Mellinda Pereira Dpenha
      Mellinda Pereira Dpenha

      Hi All,

      If some one can help me

      I was trying this similar example in table function with 2 input parameter, multiple entries

       

      I tried writing it in different way not sure weather it is correct.

      But I wanted to ask in the input parameter sematic type : what should we select as blank | unit of

       

      measure| date

      Since we keep parameter type: Direct (for users to input value)

      When I do data preview on semantic node ,I see no data

       

      Please have a look at the screenshot attached

       

      Any help is appreciated

       

      Thanks,

      Mellinda

       

      Author's profile photo Jonathan Haun
      Jonathan Haun

      One Note on Scenario 3 vs the APPLY_FILTER scenarios. If you do a PLANVIZ on both methods, the APPLY_FILTER appears to be more efficient as it applies the filter when it first accesses the table, resulting in fewer records throughout execution. The Scenario 3 methods (WHERE clause with Nested sub-select) appears to apply the filtering much later in the query execution resulting in more memory being utilized and more records being processed throughout the query execution. This might not be the case with all queries, but from the 3 limited examples I created, the APPLY_FILTER execution was more efficiency in each case.

      Author's profile photo Pablo Iniguez
      Pablo Iniguez

      Helo

      Same with my test.

      Apply filter is more efficient than splitting values, which I don't understand honestly.

      Supposedly, apply filter breaks the unfolding, but in the end, is way more fast than any other option, even doing unfolding.

      Author's profile photo Divya Kannan
      Divya Kannan

      Great blog, Thanks for sharing

      Author's profile photo Srilaxmi divi
      Srilaxmi divi

      Thank you for sharing!

       

      Could you please help me on how to filter a table based on multiple values in a HANA procedure? Is it possible to directly filter a table rather than using calculation view to filter?

      Author's profile photo Bach Duong
      Bach Duong

      Hi @Konrad Załęski Can we use TEXT or NCLOB data type instead of using varchar to get unlimited number of characters?