progress 4gl :how to retrive record from database which satisfies multiple keywords

Question

   ID   CODE        STATE   CITY            AREA
    1   SBIN0000952 ORISSA  JAIPUR          TOWN
    2   SBIN0000953 ORISSA  KURAPUT         VILLAGE
    3   SBIN0000954 DELHI   DELHI           TOWN
    4   SBIN0000955 DELHI   NEW DELHI       VILLAGE
    5   SBIN0000956 GOA     SOUTH GOA       VILLAGE
    6   SBIN0000957 GOA     PANAJI          TOWN
    7   SBIN0000958 KERLA   CHOCHIN         TOWN
    8   SBIN0000959 KERLA   TRIVANDRAM      VILLAGE
    9   SBIN0000960 ANDHRA  VIZAG           TOWN
    10  SBIN0000961 ANDHRA  HYDERABAD       VILLAGE

Given the data above, I want to search for "keywords" that are entered by a user. For example, the user might provide "kerla,town".

The program should display the record:

7   SBIN0000958 KERLA   CHOCHIN    TOWN

If multiple keywords are entered then only records that have ALL keywords should be displayed.

is there any other way without using dynamic queries,if posible please tell,thankyou — Sri Nivas, May 13 '15 at 11:47
please tell answer for this ,by using dynamic queries .@ Austin — Sri Nivas, May 13 '15 at 13:57
You should study this answer: http://stackoverflow.com/questions/29925313/how-to-search-all-tables-and-all-fields-for-a-string — Tom Bascom, May 13 '15 at 15:25

score 0 · Answer 1 · answered May 13 '15 at 18:40

Option 1: Look up the documentation on constructing dynamic queries and build all of the various equality matches for every combination of field and input value(s) you care about. This is not likely to be pretty or perform well.

Option 2: Add another character field to your table and put all of the values from the searchable fields into it. Build a word index on that field and look at the documentation for CONTAINS. It is build specifically to use a word index to find exact occurrences of text strings.

I have used option 2 many times in the past and as long as you can make sure the word indexed field is properly maintained and can deal with the few restrictions then it works well and is very fast.

score 0 · Answer 2 · answered May 13 '15 at 22:02

First, sorry for the delay. Now, I devised something that should be useful to you:

define temp-table ttTable
    field id as integer
    field state as character
    field city  as character
    field area  as character
    index idx is primary unique id.

DEFINE VARIABLE cKeyWords AS CHARACTER   NO-UNDO.
DEFINE VARIABLE i         AS INTEGER     NO-UNDO.
DEFINE VARIABLE hBuffer   AS HANDLE      NO-UNDO.

DEFINE VARIABLE cStateLst AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cCityLst  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cAreaLst  AS CHARACTER   NO-UNDO.

/* These variables are just to load the temp-table for the example */
cStateLst = 'Orissa,Orissa,Delhi,Delhi,Goa,Goa,Kerla,Kerla,Andhra,Andhra'.
cCityLst = 'Jaipur,Kuraput,Delhi,New Delhi,South Goa,Panaji,Chochin,Trivandram,Vizag,Hyderabad'.
cAreaLst = 'Town,Village,Town,Village,Village,Town,Town,Village,Town,Village'.
do i = 1 to 10:
   create ttTable.
   assign ttTable.id = i
          ttTable.state = entry(i,cStateLst)
          ttTable.city = entry(i,cCityLst)
          ttTable.area = entry(i,cAreaLst).
end.
/* For your real code, you don't need this loading part. */

update cKeyWords format "x(20)".
/* now trim the comma separated list to avoid mismatches */
do  i = 1 to num-entries(cKeyWords):
    assign entry(i,cKeyWords) = trim(entry(i,cKeyWords)).
end.
/* trailing and leading spaces eliminated, let's begin */
assign hBuffer = temp-table ttTable:default-buffer-handle.
for each ttTable:
    do i = 1 to hBuffer:num-fields:
       if lookup(string(hBuffer:buffer-field(i):buffer-value()), cKeyWords) > 0 then
          display ttTable.
    end.
end.

Notice it works with a temp-table. The only remarkable change to make it work with a table is this line assign hBuffer = temp-table ttTable:default-buffer-handle. should be

assign hBuffer = buffer <yourtable>:handle.

Some other notes: I'm cycling the fields and converting them to string prior to displaying. This is notably slower than building a query dynamically, but since you don't even know which fields they will be querying, I figure it was the best way to do it. Still, it should be much slower than having proper queries. So maybe thinking of an interface where the user also identifies the field they want to filter would make it possible for you to structure a good dynamic query and get results much more efficiently in case your table is painfully large.

Anyway, let me know if you have any questions, hope this helps.

thanks u sir,i am waiting for ur reply since yesterday afternoon,finally get it,please tell how it happen by using dynamic queries — Sri Nivas, May 14 '15 at 03:36
The thing is, with dynamic queries, you'd have to identify the field to form a query-string. So let's suppose you get an input of which filter the user wishes to filter, and the data they want to look for. Using your example, let's say — bupereira, May 14 '15 at 13:42

score 0 · Answer 3 · answered May 14 '15 at 13:59

Sri, as requested, this solution uses dynamic queries. BUT, remember, the user must enter the fields (or you should find out what they are, build them in a list in the same order the values were input). Also, this doesn't filter any values against any fields, like the example I posted before. So here it is:

define temp-table ttTable
    field id as integer
    field state as character
    field city  as character
    field area  as character
    index idx is primary unique id.

DEFINE VARIABLE cFieldLst AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cKeyWords AS CHARACTER   NO-UNDO.
DEFINE VARIABLE i         AS INTEGER     NO-UNDO.
DEFINE VARIABLE hBuffer   AS HANDLE      NO-UNDO.
DEFINE VARIABLE hQuery    AS HANDLE      NO-UNDO.

DEFINE VARIABLE cStateLst AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cCityLst  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cAreaLst  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cQueryStr AS CHARACTER   NO-UNDO.
/* These variables are just to load the temp-table for the example */
cStateLst = 'Orissa,Orissa,Delhi,Delhi,Goa,Goa,Kerla,Kerla,Andhra,Andhra'.
cCityLst = 'Jaipur,Kuraput,Delhi,New Delhi,South Goa,Panaji,Chochin,Trivandram,Vizag,Hyderabad'.
cAreaLst = 'Town,Village,Town,Village,Village,Town,Town,Village,Town,Village'.
do i = 1 to 10:
   create ttTable.
   assign ttTable.id = i
          ttTable.state = entry(i,cStateLst)
          ttTable.city = entry(i,cCityLst)
          ttTable.area = entry(i,cAreaLst).
end.
/* For your real code, you don't need this loading part. */

update cFieldLst format "x(20)". 
/* Let's assume the user types the field names correctly */
/* and the same number and order of values for fields and filters */
/* for this example, let's use 'city,area' (again, no spaces) */

update cKeyWords format "x(20)".
/* now trim the comma separated list to avoid mismatches */
do  i = 1 to num-entries(cKeyWords):
    assign entry(i,cKeyWords) = trim(entry(i,cKeyWords)).
end.
/* trailing and leading spaces eliminated, let's begin */
assign hBuffer = temp-table ttTable:default-buffer-handle.

do i = 1 to num-entries(cFieldLst):
   assign cQueryStr = cQueryStr + (if cQueryStr <> '' then ' AND ' ELSE '') + 
                      entry(i,cFieldLst) + ' = ' + quoter(entry(i,cKeyWords)).
end.
/* this is what your where clause looks like. Remove this when not testing */
MESSAGE cQueryStr
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

/* Creating dynamic query */
create query hQuery.
hQuery:set-buffers(hBuffer).
hQuery:query-prepare("FOR EACH ttTable NO-LOCK WHERE " + cQueryStr).
hQuery:query-open().
hQuery:get-first().
do  while not hQuery:query-off-end:
       display ttTable with frame f down.
       down with frame f.
    hQuery:get-next().
end.

Again, I hope this helps you.

score -1 · Answer 4 · answered May 13 '15 at 12:04

-1

if you just want any cities in your database that have kerala as a town, you should do

for each table where city = "kerla" no-lock:
   display table.
end.

for each retrieves multiple records, and the code inside executes once for every record. I don't know if this helps you, but I hope so.

answered May 13 '15 at 12:04

bupereira

1,463
8
13

i want code for multiple keywords,not for single keyword. eg is suppose user enter three keywords: kerala town delhi ,it displays result for kerala and town in one record,and delhi is in another record @bupereira – Sri Nivas May 13 '15 at 12:08
Static queries as mentioned in the answer wont suffice – Austin May 13 '15 at 12:44
Ok, Sri, so how do you want the user to enter these keywords? Are they always going to be three? Are you going to store them in a character variable, maybe a temp-table, have you decided that? – bupereira May 13 '15 at 14:33
the keywords are entered by user i.e any number of keywords,it may be one or more. i take a fill-in-field for taking it as input and search that all pairs of keywords match a database record means ,i will display on a browse...but that record must satisfy the all keywords entered by user,if not satisfied means,it will display individual keyword result. thankyou sir-------the code i write is :::: – Sri Nivas May 14 '15 at 05:06

progress 4gl :how to retrive record from database which satisfies multiple keywords

4 Answers4