Bad coding or lack of skill?

[sdjensen]

I received the daily "Progress Alert and Notification Service: OpenEdge" yesterday with the following 'solution':

ID: P187469
Title: "Using more than 255 OR's inside a WHERE clause crashes application"
Created: 05/10/2011
Last Modified: 07/03/2011
Status: Unverified
Symptoms:
The procedure has a WHERE clause inside a FOR EACH.

The WHERE clause contains more than 255 OR comparisson

example:

FOR EACH piaco WHERE
piaco.CONTO = 1117 OR
piaco.CONTO = 1118 OR
piaco.CONTO = 966 OR
.
.
.
END

Facts:
All Supported Operating Systems
OpenEdge 10.1C
OpenEdge 10.2x

Cause:
OE00207474
Fixes:
None at the moment.
Try to limit the WHERE clause to less than 255 OR's

I believe it is time for some code rewriting if one needs to have more than 255 'or' statements inside a where at the same time.

 

[Cringer]

Oh... My... Word!!!


And surely the solution is to make a noose out of strong rope...


On a serious note, I would cache the values needed into a temp-table and use that. The OR makes it a full table read anyway, so might as well scan through the whole table and cache those needed.

 

[Stefan]

If conto is an index field, the ORs will each individually match the index. I am also going to assume that this query is not hand-built, but dynamically generated based on something. The alternative:

hq:QUERY-PREPARE( SUBSTITUTE( LOOKUP( STRING( conto), &1 ) > 0, QUOTER( ccomma_separated_list ) ) )

will not break the 255 ORs, but result in a table scan.

 

[TomBascom]

It seems obvious that a list of 256 or more CONTOs didn't just fall out of the sky -- there is some underlying reason for that list which ought to exist somewhere in the database -- if it doesn't exist it should be added and indexed

But anyway... how about putting each CONTO into a single record of a temp-table and then doing a join? Something like:

define temp-table tt_conto no-undo
  field conto as integer
  index conto-idx is unique primary conto
.

/* some code to initialize tt_conto:
 */

create tt_conto. tt_conto.conto = 1117.
create tt_conto. tt_conto.conto = 1118.
create tt_conto. tt_conto.conto = 966.

/* ... */

for each tt_conto no-lock,
     each piaco no-lock where piaco.conto = tt_conto.conto:

  /* do something with piaco records... */

end.