Many-To-One selection

Question

While programming at work, a friend presented me a very interesting case: There is a table with wildcard values, such as "test". This register is able to find words containing 'test' in them, as it does not matter what comes before or after the word.

There is another table which holds registers with the desired text, such as 'this is a test' for example.

The regular access preference would be from the wildcard table to the text table, but this is not possible in my friend's scenario.

He actually came with a solution, but it doesn't feel it is as good as it could be.

His idea was to select every wildcard register and every text needed. Then, he would delete from the wild card table every register which did not have a matching target in the text table, as it is already correct and containing only pertinent values.

In ABAP this could be written as:

data: 
     wa_wildcard   type          string,
     t_wildcards   type table of string,
     t_texts       type table of string,
     wa_text       type          string,
     lv_index      type          sy-tabix,
     lv_found      type          c.

select * from zt_texts into table t_texts. "Retrieves desired texts.
check sy-subrc is initial.

select * from zt_wildcards into table t_wildcards. "Retrieves all widlcards.
check sy-subrc is initial.

loop at t_wildcards into wa_wildcard. "Checks every Wildcard
  move lv_index to sy-tabix. "Stores the iteration index for Wildcard table.

  loop at t_texts into wa_text. "Checks if the actual wildcard matches any text retrieved.
    if wa_text cp wa_wildcard-value. "If text contain parts of the wildcard string, it matches
      move 'X' to lv_found. "Found a match, may exit the loop!
      exit.
    endif.
  endloop.

  if lv_found ne 'X'.
    delete t_wildcards index lv_index. "removes the wildcard if no match was found.
  endif.
endloop.

Since this is done in ABAP, i thought there would be a select statement capable of doing this validation process directly into the database, for the wildcard table may be too big to select everything, iterate, and process.

EDIT: The general Idea is to use the text to find appropriate wildcards for it, without testing every single wildcard with every single text. I would like to know if this is possible to be implemented in any solution, be it database-oriented, i.e in a select statement or in pure code.

How big is too big? And btw, that ABAP Coding should be taken out and shot. Please do NOT take this as an example of how to write ABAP programs. — vwegert
This is not a proper code, it is something I wrote just to explain how the scenario works and for people with no ABAP knowledge to understand it better. The 'big' factor is not only number of entries, but unnecessary processing. Imagine the wildcard table had 200,000 entries, the text table had 600,000 and no matches were found (hypothetical scenario). — Zeh
There is no magic that will save you from checking every wildcard against every table entry. The only question is whether you want to offload that processing to your most expensive resource (the database servers) or keep it in the ABAP processing... — vwegert
Exactly. However, there may be some optimizations before that. There are, of course, the check for duplicate entries, blank entries and whatnot. I was thinking about an operation that is the exact opposite of CP (contain parts) since it forces the check of the wildcard on the text, while the other way around, passing the text and returning matching wildcards, seems to have a better performance and readibility. — Zeh
It might be just my understanding, but what you write does not make any sense to me. "Passing the text and returning wildcards" - how is that supposed to work at all? — vwegert

Eric Eric · Accepted Answer · 2013-06-29T22:35:58

This could be a better method depending on how many entries you expect to select. It requires as many database accesses for ZTEXT as there are entries in ZWILDCARD, but if that's a small number, this will reduce the load.

select * from zwildcard into t_wildcards.

loop at t_wildcards into  wa_wildcard.
  select * from ztext appending table t_texts
    where value LIKE wa_wildcard.
  if sy-subrc is not initial.
    delete t_wildcards index sy-index.
  endif.
endloop.

This is assuming ZWILDCARD has a list of entries with SQL wildcards (% and _, not * and +) on either side. If not, you'd need to add the necessary formatting at the beginning of the loop.

Also, if you only cared about t_wildcard and not t_text, the database access could be a select single, further reducing the load.

Many-To-One selection

1 Answers