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,
Regards
David