The following steps show how to implement fuzzy searching using SAP HANA.
In this example we will be performing a fuzzy search on first name and last name of business partners in SAP CRM.
Note: You can only do this through SAP HANA Studio or equivalent tool such as Eclipse Neon/Eclipse Mars etc. It cannot be done using the SAP GUI editor. The reason for this will be explained in the steps below.
Create Your CDS
Right click on your system, select New > ABAP Repository Object..
From the popup window select Core Data Services > Data Definition.
Fill in the fields:
This will open the code editor.
Type your view details in here
@AbapCatalog.sqlViewName: 'ZVWCDSDEMO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS View Demo'
@OData.publish: true
define view zvw_cds_demo as select from but000 as b {
key b.partner,
b.name_last,
b.name_first
}
NOTE: sqlViewName cannot be the same as the name of the view you define.
View Created
Right click and select refresh:
You will now see your new view:
Class Generated
Your class will now have been generated:
Implement Inferface
The next step is to implement the interface IF_AMDP_MARKER_HDB in your class.
NOTE: This will make your class READ ONLY in the SAP GUI. Hence why the Eclipse IDE is needed to continue the implementation of the fuzzy search.
In Eclipse click on Class and in the code editor after the Public Section declaration implement the inteface:
public section.
interfaces IF_AMDP_MARKER_HDB .
Define Structure for returned data
Straight after the interface declaration, define the structure for the data you want returned from your fuzzy search.
In this case Score, Partner, Name_First and Name_Last.
types:
BEGIN OF ty_partner_view,
score type CRMT_UBB_FACTOR,
partner TYPE bu_partner,
name_first TYPE bu_namep_f,
name_last TYPE bu_namep_l,
END OF ty_partner_view .
*Define the type
types:
tt_partner_view TYPE TABLE OF ty_partner_view .
Define the Method
In the class definition, define the method.
class-methods FUZZY_SEARCH
importing
value(IV_FIRST_NAME) type BU_NAMEP_F
value(IV_LAST_NAME) type BU_NAMEP_L
exporting
value(ET_DATA) type TT_PARTNER_VIEW .
Implement the Method
METHOD fuzzy_search BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING zvwcdsdemo.
et_data = select distinct score( ), partner, name_first, name_last
from ZVWCDSDEMO
where
(
contains( name_first, :iv_first_name, fuzzy(0.5) ) and
contains( name_last, :iv_last_name, fuzzy(0.8) )
)
ORDER BY score( ) desc;
endmethod.
Test the Fuzzy Search
Goto SE24 and enter your class name.
Select the test button.
Click on the Fuzzy_Search method.
Enter your parameters:
Select the Debugging button.
Step through the code and select the contents of ET_DATA when the select statement has been executed to see the results:
As the results show, an exact match returns a score of 1.000. Non exact matches have a lower score.
End.
Hi @David Bibby,
Thanks for your information.
I have some questions based on your blog:
Best wishes
Vincent
Hi Vincent,
I guess you won’t see this, but for anyone else who reads this blog and misses it, like I did:
You must have the annotation
in the header of your CDS view, in order for the class to be automatically generated as shown in the blog.
I found that it also works if I create the class manually with just the elements described in the blog. (All the oData stuff added by SADL, are apparently not required for using the fuzzy search.)
Regards,
Margaret
Hi Vincent,
Regards
David
Hello David,
i am getting below error in the class. kindly help me to identify what wrong i am doing.
my system is ECC 7.50 and Data base is HANA.
SQLSCRIPT message: return type mismatch: Procedure ZCL_ZVW_CDS_DEMO=>FUZZY_SEARCH: Attribute name “SCORE” different from attribute name: “ET_DATA”.”SCORE()” &A0&A1&A2&A3&A4&A5&A6&A7&A8&A9