OpenEdge currently doesn't have an 'IN' operator like SQL has. Example:
SELECT * FROM customer WHERE state IN ('MA', 'NY', 'FL').
There are some ways to do this in OpenEdge:
1) FOR EACH customer WHERE LOOKUP(state,"MA,NY,FL") >0
This always results in a table scan. There may also be formatting issues which non character fields like decimal keys like dynamics obj fields.
2) FOR EACH customer WHERE (state = 'MA' OR state = 'NY' OR state = 'FL')
if there is an index on state the second query would be indexed. This also supports passing the values in the right datatype without formatting issues.
But If we have *many* values and if we generate the statement then there may be a problem with the statement length (long field name and long value like guid). The query string is a character and not a longchar and limited to about 32k characters.
What do you think. Are you missing this too?
@development: do you already have this on your list? :-)
Hi Thomas,
I'd love to see that feature as it would make lots of queries easier to use.
Are you aware of the Ideas section on Communities: http://knowledgebase.progress.com/articles/Article/P11255?popup=true
Cheers,
Mike=
Hi Mike,
no, I was not aware of this ideas section. But I will submit my request there.
Thanks,
Thomas
I usually get around this by creating a temp-table with the values I need and adding it as a join. But it's cumbersome to achieve if there's lots of possible values.
Using a temp-table will result in all records being pulled from the database to the client.
I didn't know that. It's not usually an issue in our application. But thanks for the info.
Indeed - I was in auto-inner-join mode this morning ;-)