Kapil Patil wrote:

> As per my knowledge I know that IN CLAUSE have limitations of only 1499
> elements. (firebird v2.0)
>
> I have one query where IN CLAUSE elements exceeds above 4000 therefore
> firebird gives error...
>
> I tried entering the elements in temp. table and then using the
>
> SELECT ........ from TABLENAME where ...... in (Select .. from
> TEMPTABLE)
>
> It works, but quiet slow because I have to first insert the elements in
> TEMP TABLE and
> then fire the main query as shown above.
>
> I Also tried using combination of IN and OR Operator as
>
> select ... from tablename where .. in (1,2,3,4,5,6....,1499) or ... in
> (1500.......) or ... in(.......)
>
> But this works very very slow taking around 8 to 10 minutes and
> sometimes even more, so not very effective query
>
> Some one suggested me to break the query in two or more
> like first 1499 elements in one query and then fire and get the records
> and then
> next 1499 elements and get the records and so .. on
>
> but the problem is that I want to order by clause and with this approach
> it is not possible to sort the data....
> and secondly If query contains aggregate functions like SUM, MAX, MIN
> ..... in that case
> firing query with first 1499 elements and so..on will give me wrong
> results
>
> SO, Is there any way to overcome this limitations with out creating any
> TEMP TABLE
>

Typically if you need a lot of elements in a IN clause, then, typically
the design is broken. The question becomes where do the values for the
IN clause come from. Could those come from a table, replacing the IN
with a join.

W