Informix Error -26918: The SQL statement has more than 131071 items in an IN list.
Cause and resolution
The SQL statement has more than 131071 items in an IN list.
The maximum number of items in an IN list in an SQL statement is 131071. Bigger statements take too long to prepare and execute, and can usually be rewritten, often by creating and joining with a temporary table.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-26918 fires when an IN list in an SQL statement holds more than 131071 items.
- An IN (...) list with more than 131071 values, typically generated by an application that builds the list from a large result set.
Solutions / Resolution
- Put the values in a temporary table and join to it, per the official guidance, instead of listing them in the statement.
- Split the statement into several smaller statements if a join is not practical.
Examples
Replacing a large IN list with a join
CREATE TEMP TABLE ids (id INT) WITH NO LOG;
-- load the values into ids, then:
SELECT t.* FROM bigtab t, ids WHERE t.id = ids.id;
Diagnostic Checks
- Count the items the application puts in the IN list and compare with 131071.
Related Errors / Related Topics
Related: -26909.
More than 131071 items in an IN list — use a temporary table and join instead.