Skip to Content

AMDP fuzzy search for multiple search terms

Dear all,

I got a requirement to do a system wide fault tolerant search for an e-mail address in database table ADR6. I managed to do this with this AMDP method:

  METHOD amdp_search_dupl_work_addr
      BY DATABASE PROCEDURE
      FOR HDB
      LANGUAGE SQLSCRIPT
      OPTIONS READ-ONLY
      USING adr6.

    et_duplicates = select to_decimal(score( ),3,2 ) as score,
                           smtp_addr as email
                    from adr6
                    where contains(smtp_addr, iv_email, fuzzy(:iv_score) )
                     ORDER BY score desc;

  ENDMETHOD.

As you can see with parameter 'iv_email' I am able to search for one e-mail.

Now, the requirement changed and it should be searched for multiple e-mails at once.

I read information about APPLY_FILTER (Handling of SELECT-OPTIONS parameters within AMDP), but I think this is not able to cover the fault tolerant search I already have done for searching just one e-mail address.

As I understand it, it would filter the table before fuzzy search would be executed. If that's the case, it makes the whole point of fuzzy searching useless.

In addition, I checked the option of having multiple 'contains( ... )', but I don't know how I would create them dynamically depending on how many e-mail addresses should be searched.

Is there a way on how I can achieve the described requirement using AMDP? Please let me know your thoughts and ideas.

Best regards

Sascha

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 31, 2019 at 09:35 AM

    I was able to implement multiple CONTAINS(), but only hardcoded. It would really be nice if this could be build dynamically. Does anybody have an idea?

    My goal is to have a fault-tolerant search for multiple e-mail addresses at once.

    Here is the hardcoded version of having multiple CONTAINS().

    METHOD amdp_search_dupl_work_addrs
          BY DATABASE PROCEDURE
          FOR HDB
          LANGUAGE SQLSCRIPT
          OPTIONS READ-ONLY
          USING adr6.
    
        et_duplicates = select to_decimal(score( ),3,2 ) as score,
                               smtp_addr              as email
                        from adr6
                        where ( contains(smtp_addr, iv_email0, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email1, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email2, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email3, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email4, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email5, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email6, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email7, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email8, fuzzy(:iv_score) )
                                OR contains(smtp_addr, iv_email9, fuzzy(:iv_score) ) )
                         ORDER BY score desc;
    
      ENDMETHOD.
    

    Would really appreciate it, if somebody can share their thoughts.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.