CODEWITHSUNDEEP
informixwhere-clause
Wonko the Sane
Wonko the Sane

Reputation: 10813

Informix: Limitation on Number of Items in IN Clause?

Is there a limitation to the number of items that can go into an IN clause in an Informix query (like the 1000 item limit in Oracle)?

We have a "large" (perhaps 2000) list of item numbers being passed through a web service for selection, so there isn't really any context available beyond the list of items.

Upvotes: 4

Views: 5756

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

The upper limit is imposed by the space that will be taken to create the IN list and the 64 KiB limit on statements. You can typically get to several thousand smallish (6-7 digit) integers without much problem at the syntactic level.

However, you may find that the performance is not as good as creating a temporary table, inserting the several thousand values into that, and then writing the main query to join with that temporary table.

Upvotes: 2

Related Questions