Enhancement request: 'IN' operator for where clause

Posted by Thomas Wurl on 10-Aug-2014 23:00

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? :-)

 

All Replies

Posted by Mike Fechner on 10-Aug-2014 23:10

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=

Posted by Thomas Wurl on 11-Aug-2014 02:10

Hi Mike,

no,  I was not aware of this ideas section. But I will submit my request there.

Thanks,

Thomas

Posted by James Palmer on 11-Aug-2014 02:22

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.

Posted by Stefan Drissen on 11-Aug-2014 02:32

Using a temp-table will result in all records being pulled from the database to the client.

Posted by James Palmer on 11-Aug-2014 02:45

I didn't know that. It's not usually an issue in our application. But thanks for the info.

Posted by Mike Fechner on 11-Aug-2014 02:47

Using a temp-table will result in all records being pulled from the database to the client.
 
That depends on the order of the tables in the query.

Posted by Stefan Drissen on 11-Aug-2014 02:52

Indeed - I was in auto-inner-join mode this morning ;-)

This thread is closed