IN List (How many maximum?)

  • 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

  • In is unlimited AFAIK. But usually a join is a better way to go as rsharma already stated.

  • 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. 

  • 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

  • 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 .

  • 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

  • Good point.... I'm hoping you're not using dynamic sql for this?

  • 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.

  • 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

  • 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.

  • 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.

  • 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