October 26, 2005 at 8:54 am
We have a query that is blowing off.
What I need to know is how many items are allowed in an IN list?
Example
SELECT V1, V2, V3 FROM Table1 WHERE V2 IN (?,?,?,?..........)
I am not finding anything in BOL.
Thanks.
Randy
October 26, 2005 at 9:00 am
In is unlimited AFAIK. But usually a join is a better way to go as rsharma already stated.
October 26, 2005 at 9:00 am
Why do you need many items in the IN (, , , ) list ? Can't you write a filter condition or a sub-query (or a co-related sub-query) with filters that will qualify for all those values that you want to put into the IN clause ?
Example:
SELECT V1, V2, V3 FROM Table1 WHERE V2 > ? AND V2 < ?
or
SELECT V1, V2, V3 FROM Table1 WHERE V2 IN (SELECT X FROM T WHERE....)
or
SELECT V1, V2, V3 FROM Table1 WHERE EXISTS (SELECT 1 FROM T WHERE COL1 = TABLE1.V2 AND COL2 = ?)
Basically some SQL way of identifying those IN list data values.
October 26, 2005 at 9:10 am
Reason for this method is, the query is from a COGNOS report prompt. When a user selects a contractor, a distinct list of recipients is generated and the only option for the user is to select individuals or hit a 'select all' button.
I have the developer looking at adding an '-All-' choice at the top of the dropdown list to leave that filter off.
However, I was hoping to see a definitive maximum number that can be used in the IN () list.
Thank you.
Randy
October 26, 2005 at 9:14 am
I don't think there should be any limit on the in(), but I'd definitly go with the all option leaving that out of the query if possible .
October 26, 2005 at 9:23 am
I don't think there is a limit on the IN clause, but might you be hitting the 8000 char limit on SQL statement?
Mark
October 26, 2005 at 9:26 am
Good point.... I'm hoping you're not using dynamic sql for this?
October 26, 2005 at 11:33 am
Yes, it is dynamic sql generated by Cognos. I havent been able to get the sql statement so I can run it in QA and optimize it.
October 26, 2005 at 11:37 am
Perhaps you may want to run Profiler and capture the SQL it is generating to see. You should be able to filter in Profiler by something (workstation? application? database? to get only the queries you want.
Hope this helps.
Mark
October 27, 2005 at 1:55 am
I've had this issue before, runnning a query from an Oracle db through DTS. I cannot exactly remember the amount, but it was something in the region of 2000 or 3000. This might however be an Oracle limit, and not SQL Server related at all.
Martin.
October 27, 2005 at 3:11 am
This is one of those questions where, 'if you need toknow the answer, you're probably doing something wrong'!
I would suggest writing the list to a temporary table and doing a join on that - but the 8k limit also is a good point. With the temporary table option, you could keep passing values in chunks and accumulating in the table, then running the query. This would be unlimited for all practical purposes.
October 28, 2005 at 7:45 am
Thanks everybody for your ideas. I had thought the problem was the number of vars in the IN list but probably the total size of the SQL statement was the problem.
I kicked the problem back to the developer for him to change his method of selecting individuals, instead of selecting all individuals individually, to just omit that particular filter or use something like WHERE billnum in ('%') if that filter needs to be included by Cognos.
Thanks again.
Randy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply