sql "IN" clause gotcha

Though standard sql puts no limit on the number of entries you can put inside an IN clause, but various vendors typically put a limit. Oracle puts a limit of 1000 to it. This caused a bug in our app as we dynamically generate a sql query where number of entries went beyond 1000 today.

You can put any one of the resolution stated below(in decreasing order of preference)

1. Change your logic so that number of entries never go beyond 1000(it may be a good practice in general to follow).
2. See if you can use "BETWEEN" clause for your particular case.

Instead of using "Select blah from tablexyz WHERE col IN (c1,c2,....c50000)"
3. Use "Select blah from tablexyz WHERE col IN (select id from ctable)" and the subquery "select id from ctable" returns resultset containing c1,c2,...c50000.
4. Use "Select blah from tablexyz WHERE col IN (c1,c2,...c1000) OR col IN (c1001,c1002,...,c2000) OR ... OR col IN (c49001,c49002,...,c50000)".
5. Use multiple sql queries each with one IN clause containing 1000 entries.

Much of above came from this thread.