|RE: [firebird-support] limit to number of OR conditions in a where clause
|Svein Erling Tysvær
>Firebird 1.5 classic on Windows.Long before you reach this number of ORs, you ought to consider whether your design is ideal or not.
>Is there a limit to the number of OR conditions you can put in a where clause?
>It looks there is as I get unexpected end of command when there are some 2000 ors, but maybe that is due
>limitations of the control holding the SQL. I found some 150 ors is still fine.
I don't know why you reach 2000 ORs, but I'd probably recommend you to add another table:
CREATE GENERATOR MyQueryIDGen;
CREATE TABLE RBS_OR(
ID INTEGER NOT NULL PRIMARY KEY,
MyVarChar VARCHAR(255); /*Or longer if necessary*/
(It might or might not be desirable to have an index for MyQueryID)
Then, rather than using
WHERE FieldA STARTING WITH 'bd' or FieldA STARTING WITH 'bi' or ...
I'd grasp the next value from the generator (programmatically), insert all starting with values into RBS_OR and use
FROM RBS_OR R
JOIN Table1 T1 ON T1.FieldA STARTING R.MyVarChar
WHERE R.MyQueryID = <GraspedAndInsertedValue>
The above will only be required if you have simultaneous access from several users, each building their query dynamically. If the query is static or concurrent use of different queries isn't required, then things can be simplified.