fuzzy search with multiple name fields

[Koen_VL]

Hello,

I'm trying to implement a fuzzy search to find employee names in the HR table PA0002.

The search I want to perform is on firstname and lastname. The challenge with the PA0002 table is that there are multiple fields to hold first and last name.

For firstname there is the field VORNA which holds the offical first name and RUFNM which holds the known as.

For lastname there is the field NACHN which holds the official lastname as well as NAME2 which holds the name at birth.

The fields VORNA and NACHN are always filled, but the alternative names can be filled or empty. Some examples below.

PERNR	VORNA	RUFNM	NACHN	NAME2
1	Donald	Don	Duck	Chicken
2	Daffy		Duck	Rabbit
3	Road		Runner

For the fuzzy search it is relevant that firstname is only found in the fields VORNA, RUFNM as well as last name in NACHN, NAME2.

the ideal CDS view I created for this is below, but gives a syntax error, which is expected as multiple freestyle contains are not allowed.

Anyone has an idea on how this could be achieved?

CLASS zz9xx_cl_ac_fuzzy_search_prem DEFINITION
   PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_pa0002,
             fuzzy_score TYPE zfuzzy_similarity,
             vorna       TYPE pa0002-vorna,
             rufnm       TYPE pa0002-rufnm,
             nachn       TYPE pa0002-nachn,
             name2       TYPE pa0002-name2,
             pernr       TYPE pa0002-pernr,
           END OF ty_pa0002.

    TYPES: tt_pa0002 TYPE STANDARD TABLE OF ty_pa0002.

    CLASS-METHODS get_fuzzy_pa0002 IMPORTING VALUE(im_first_name)       TYPE string
                                             VALUE(im_last_name)        TYPE string
                                             VALUE(im_fuzzy_similarity) TYPE zfuzzy_similarity
                                   EXPORTING VALUE(t_pa0002)            TYPE tt_pa0002.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zz9xx_cl_ac_fuzzy_search_prem IMPLEMENTATION.
  METHOD get_fuzzy_pa0002 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
                      USING pa0002.
    t_pa0002 = select  SCORE() as fuzzy_score, vorna, rufnm, nachn, name2, pernr
              from pa0002
              where ( (  contains (  ( vorna, rufnm) ,  :im_first_name , fuzzy( :im_fuzzy_similarity ) )
                      OR contains (  ( nachn, name2) ,  :im_last_name , fuzzy( :im_fuzzy_similarity ) )

                       ) and endda = '99991231'
                  ) ORDER BY fuzzy_score DESC
                  ;


  ENDMETHOD.

ENDCLASS.

thanks

Koen

[Vitaliy-R]

Koen, I would suggest adding `ABAP` to SAP Managed Tags in your question to make it better discoverable with the ABAP audience.

[thomas_mller13]

I.e. if you run it without "or" it works?

[NISHANT_R · Accepted solution]

Hi koen,

Refer this link 

https://help.sap.com/docs/SAP_HANA_PLATFORM/691cb949c1034198800afde3e5be6570/9a4da8f6f23b4ebf95c98f1...

[Koen_VL]

thanks - the "or" suggestion works fine.

the code now looks like this:

CLASS zz9xx_cl_ac_fuzzy_search_prem DEFINITION
   PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_pa0002,
             fuzzy_score TYPE zfuzzy_similarity,
             vorna       TYPE pa0002-vorna,
             rufnm       TYPE pa0002-rufnm,
             nachn       TYPE pa0002-nachn,
             name2       TYPE pa0002-name2,
             pernr       TYPE pa0002-pernr,
           END OF ty_pa0002.

    TYPES: tt_pa0002 TYPE STANDARD TABLE OF ty_pa0002.

    CLASS-METHODS get_fuzzy_pa0002 IMPORTING VALUE(im_first_name)       TYPE string
                                             VALUE(im_last_name)        TYPE string
                                             VALUE(im_fuzzy_similarity) TYPE zfuzzy_similarity
                                   EXPORTING VALUE(t_pa0002)            TYPE tt_pa0002.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zz9xx_cl_ac_fuzzy_search_prem IMPLEMENTATION.
  METHOD get_fuzzy_pa0002 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
                      USING pa0002.
    t_pa0002 = select  SCORE() as fuzzy_score, vorna, rufnm, nachn, name2, pernr
              from pa0002
              where ( (  contains (  ( vorna, rufnm) ,  :im_first_name , fuzzy( :im_fuzzy_similarity ) )
                      AND (  contains (  nachn ,  :im_last_name , fuzzy( :im_fuzzy_similarity ) )
                             or contains (  nachn ,  :im_last_name , fuzzy( :im_fuzzy_similarity ) ) )

                       ) and endda = '99991231'
                  ) ORDER BY fuzzy_score DESC
                  ;


  ENDMETHOD.

ENDCLASS.