CODEWITHSUNDEEP
openedgeprogress-4gl
Bharat
Bharat

Reputation: 177

Progess 4GL - How to filter multiple records using AND operator in one table field?

I want to check and filter only if the table has value1 = 005 and value1 = 009. But it seems below query is not helping me. I dont know where I am making mistakes. Kindly help to solve this. Note - I cannot use where not as it may have many different value stored in value1 field

DEFINE TEMP-TABLE test NO-UNDO
FIELD value1 AS CHARACTER
.

EMPTY TEMP-TABLE test.

CREATE test.
ASSIGN
  value1 = "005".

CREATE test.
ASSIGN
  value1 = "009".

CREATE test.
ASSIGN
  value1 = "001".

FOR EACH test NO-LOCK
   WHERE value1 <> ""
   AND (value1 = "005" AND value1 = "009")
  :

   MESSAGE YES.
END.

Upvotes: 0

Views: 325

Answers (4)

Tom Bascom
Tom Bascom

Reputation: 14020

Another option if you are, maybe, looking for some additional fields might look something like this:

define buffer test005 for test.
define buffer test009 for test.

for each test005 no-lock where test005.customer = 1 and test005.value1 = "005",
    each test009 no-lock where test009.customer = 1 and test009.value1 = "009":

  display test005.customer.

end.

Upvotes: 2

carl verbiest
carl verbiest

Reputation: 1295

You can use can-find

if can-find(first test WHERE value1 = "005") 
   AND can-find(first test WHERE value1 = "009")
then message yes.

It is safest to always use can-find(first if you're looking for a non-unique value

Upvotes: 3

Tom
Tom

Reputation: 5667

If you want to check if both records are present you could do :

DEFINE VARIABLE isPresent005 AS LOGICAL NO-UNDO.
DEFINE VARIABLE isPresent009 AS LOGICAL NO-UNDO.
DEFINE VARIABLE bothPresents AS LOGICAL NO-UNDO.

FIND FIRST test WHERE test.value1 = "005" NO-LOCK NO-ERROR.
isPresent005 = AVAIL test.

FIND FIRST test WHERE test.value1 = "009" NO-LOCK NO-ERROR.
isPresent009 = AVAIL test.

bothPresents = isPresent005 AND isPresent009.

But, if you only want to get these 2 records, you should use OR :

FOR EACH test WHERE test.value1 = "005" OR test.value1 = "009" NO-LOCK :
    /*do stuff*/
END.

Upvotes: 2

nwahmaet
nwahmaet

Reputation: 3909

It looks like you're looking for an OR ooperation, rather than AND.

Upvotes: 2

Related Questions