Hello,

We encountered a similar problem with some of our reports. The query
is built dynamically by the Report Filter UI (at least the CSV bit
inside the IN operation is dynamically changed). For a large
customer, we hit the limitation of IN, and our workaround advice for
the moment is to basically run the report A-K then L-Z.

I read somewhere that IN is internally converted to a massive OR list
(or vice versa). If this is the case, is the compiler scratch space
limitation to do with this conversion.

Also, the question about the maximum statement size beckons. Are you
likely to hit the maximum possible statement size with a query like

select name
from employee
where id in (1,2,3,.....,1499) or
id in (1500,...etc) or
id in (etc) or
id ....
..

Particularily if this is optimised into a series of OR statements.

The way I theorised to programatically work around this problem is
here:

I have a temporary table

ReportScratch
(
ReportID integer,
TransactionID integer,
SomeValue integer
)

The pseudo code is:

1. delete from ReportScratch where ReportID = 1 and TransactionID =
CURRENT_TRANSACTION;
2. For every checked item in the checklistbox; insert into
ReportScratch(1, CURRENT_TRANSACTION, :CheckedID);
3. I then either join to this table in my query or include it in
a "where ID in" clause depending on which performs better in testing.
4. delete from ReportScratch where ReportID = 1 and TransactionID =
CURRENT_TRANSACTION;

Do not commit until after 4, otherwise you risk filling the table
with irrelevant data. If there is a problem before or during 4 (eg
computer crash), your transaction will not be committed.

Disclaimer: I haven't implemented this so I don't know how well it
works in practice nor have I checked the best way of implementing
point 3.

Mind you if combining the IN operators works well then I will just
use that approach.

Hope that gives some ideas
Adam