CODEWITHSUNDEEP
fielddelimiterprogress-4glopenedge
mac100
mac100

Reputation: 137

How to query a table based on data separated by delimiter in progress openedge 4gl?

I have a string 

a = "test1;test2;test3;test4;test5;".

I need to query a table ( say table ) with each data separated by ";"

My query is :

For each table where table.field1 matches "*test1*" no-lock:

    disp table.field1 table.field2

end.

Upvotes: 0

Views: 97

Answers (2)

jdpjamesp
jdpjamesp

Reputation: 772

Whilst your solution will work ok, there is a lot of overhead to it, particularly if the 'for each' is a slow query. You'd be better off creating a temp table of the values you want and adding that to the query. And as you're using 'matches' make sure field1 is in a decent index or performance will be horrible! 

DEFINE VARIABLE v-cnt AS INTEGER     NO-UNDO.
DEFINE VARIABLE segment AS longchar   NO-UNDO.

define temp-table tt-element no-undo
  field element as character
  index elidx is primary element.

assign segment = "abc;bcd;efg;".

do v-cnt = 1 to num-entries(segment,';'):
  create tt-element.
  tt-element.element = entry(v-cnt,segment,';').
end. 

for each tt-element,
  each table1 no-lock
  where table1.field1 matches '*' + tt-element.element + '*':
  message table1.field1
    view-as alert-box info buttons ok.
end.

Upvotes: 1

mac100
mac100

Reputation: 137

DEFINE VARIABLE v-cnt AS INTEGER     NO-UNDO.
DEFINE VARIABLE segment AS longchar   NO-UNDO.
DEFINE VARIABLE eachelement AS CHARACTER   NO-UNDO.

assign segment = "abc;bcd;efg;".

DO v-cnt = 1 TO num-entries(segment, ";") - 1  :

ASSIGN eachelement = string(entry(v-cnt, segment, ";")).

FOR each table1 where table1.field1 matches "*" + eachelement + "*"  no-lock:

       MESSAGE table1.field1 
           VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
END.

Upvotes: 0

Related Questions