Alternative to temporary tables/tables for storing fuzzy/fulltext search results in SAP HANA.

[former_member800291]

Hello Experts,

I am building a search tool using SAP HANA and SAC, (we do not have an option to develop an UX, so SAC is our only Frontend solution). Requirement is to search through various material master fields and return the search result along with a score. Users will search with 1 to 10 different strings/texts based on the need. The result should be returned as explained below:

For eg: In this instance, user searches with three different strings/search terms.

Search term 1: 'O-AAA' returns material M0AXXXX

Search term 2: 'MAT123' returns material M0AXXXX and M0AYYYY

Search term 3: 'TEST' returns material M000000

following is the expected output:

My initial plan was to:

1. Create a attribute view with all the required fields - (Material, Long Description, Short Description).

2. Develop a Stored Procedure with select statement on this attribute view with 'contains' predicate and in the search term of the contains predicate, pass the user search terms as input parameters.

3. Store the result in global/local temporary table. This is required, as I need to calculate the score for every search term (and the inbuilt Score() function doesn't satisfy my above requirement of score calculation). So it is required for me to search each search strings separately through a cursor and loop statement on each input parameter. Something like below:

DECLARE CURSOR c_cursor1 FOR SELECT :IP_SEARCHTERM AS F_SEARCHTEXT FROM DUMMY;

--Note: By default multiple input value will not work on where conditions of select statements inside a procedure, so I will use a separate table function to split multiple input values as separate single value.

FOR tab1 AS c_cursor1 DO

INSERT INTO "SCHEMA"."Global_Temp_Table"

SELECT distinct tab1.F_SEARCHTEXT as SearchTerm, MATERIAL, LONG_DESC, SHORT_DESC, 1 as SCORE from ATTRIBUTE_VIEW Where Contains ((MATERIAL, LONG_DESC, SHORT_DESC), tab1.F_SEARCHTEXT);

4. Then call this store procedure in table function and do a select from the Global_Temp_Table and send the result set to SAC through wrapper Calculation View on top of this table function. Here I can calculate the score percentage by taking the sum(Score) for each material and divide it by the total number of input parameters.

For eg: As per the above example I will get the below result for material M0AXXXX:

Now I can get the sum(Score) = 2 and count of input parameters = 3 ('O-AAA', 'MAT123', 'TEST') and calculate the score percentage.

One might ask why not do the above search directly in a table function. But to calculate this score, I felt it is necessary to store the result set for every search term intermediately somewhere. If not for this Score, I could have done this whole thing using a table function.

But then I realized that the table functions can only execute read-only procedures. So the above solution will not work for me as long as I am trying to consume it through a TF and then CV to SAC. I also got a hint that ARRAY can be used in place of internal tables in HANA SQLScript, but I could not find a sample code for the same with my scenario, where Contains predicate needs to be used with input parameter.

I really appreciate any help here.

Thanks,

Siva

[former_member800291 · Accepted solution]

Found this blog:

https://blogs.sap.com/2016/07/07/hana-tips-tricks-issue-2-hacking-information-views/

I used Union All approach mentioned here and it solved my issue.