on 2024 Apr 23 7:58 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.