Implementing Fuzzy Search Using CDS & SAP HANA

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.