Fee text search from prefixed database tables ?

[Former Member]

Hi experts ,

I have a requirement where I have to create a free text search using WDA. Main goal is to enable the end user to search from few prefixed database tables. User should also be able use wildcard characters like * in case he does not know the full search term.

I am interested in the best approach to go about this requirement. Anybody who has already faced similar scenario is welcome to help.

scenario explained again below :

best regards ,

Amit.

[former_member211591 · Accepted solution]

Hi Amit,

I've got a different approach. It bases upon search in an internal table.

First select your DB-Table into an internal table (lt_db).

The main search is done with following loop. Result will be in table lt_output.

loop at lt_db into ls_db

                    where ( patnr cs lv_begr          "Patient number
                                 or pname cs lv_begr  "Patient name
                                 or planb cs lv_begr     " A flag
                                 or gbdat cs lv_begr    "Birthdate     

                                 "....

               ).

               append ls_db to lt_output.

endloop.

This search looks for lv_begr in fields patnr, pname, planb and gbdat.

Pro:

• you don't need to consider contextsensitivity.
• lv_begr is handled like "*lv_begr*" without need to enter  *.

Contra:

• Maybe performance issues if lt_db is too large.
  • try do minimize lt_db when populating it.

By using this loop and handling internal tables you can build your own search logic.

E.g. I extended my search by following feature.

If lv_begr contains spaces they are considered as AND.

E.g. Search for lv_begr = "tom 1980" gives me all db-entries which contain string tom and 1980, thus I find all patients with "tom" in their names (field pname) and born in 1980 (field GBDAT).

BR

ismail

[Former Member]

First thing you have to do is get the search term entered by user on screen. Then you can just replace '*' with '%' in the entire string using REPLACE statement. Now in the select query use LIKE addition to fetch database records.

But there is one more thing you need to take care of. 'Case sensitivity'. If your database has random combinations i.e. neither all are small letters nor all are capital letters, the query wont fetch records if you don't match exact case that is maintained in the database.

If all are capital letters or small letters , then you can convert your search term to upper case or lower case as required.

If the database entries are a mix then, say you want the search on FIELD1, create an additional field say SEARCH_FIELD1. Now whenever there is any entry in the database table FIELD1 say 'Abc', convert this entry to upper case 'ABC' and enter in SEARCH_FIELD1. This way the exact entry is maintained in FIELD1 but while searching you can simply convert your search term to upper case and put query against SEARCH_FIELD1 instead of FIELD1.