Progress OpenEdge like SQL where IN?

Question

I can do this with a string and it works great. example:

for each emp no-lock where
   lookup(emp.name, "james,john,kerry,david") <> 0:
  /* Do something */
end.

Now I have emp.ID that is not a string (it's an integer) how can I do where IN ? I tried similar method as above. but it gives me an error, it says "incompatible data types"

for each emp no-lock where
    lookup(emp.ID, "1,5,89") :
  /* Do something */
end.

how do I do this ? thank yo u

Tom Bascom Tom Bascom · Accepted Answer · 2016-06-10T18:13:27

OpenEdge has a SQL-92 engine but you aren't using that. You are using the 4gl engine. There is some limited SQL-89 syntax embedded inside the 4gl engine but it is a bad idea to try to use it. It only leads to pain and suffering

The 4gl has no IN function. To do what you are trying to do with a variable set of integers you would probably want to first create a temp-table and then join the TT with your real table. Something like this:

define temp-table tt_intList
  field f1 as integer
.

create tt_intList.
tt_intList.f1 = 1.

create tt_intList.
tt_intList.f1 = 5.

create tt_intList.
tt_intList.f1 = 89.

for each tt_intList, each emp no-lock where emp.Id = tt_intList.f1:
  display emp.Id emp.name.
end.

Progress OpenEdge like SQL where IN?

2 Answers