AMDP fuzzy search for multiple search terms

[sascha_roehm]

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

[sascha_roehm]

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.