sql coding error with in statement

I have a problem with my sql statement. Its within a function that passes a list of student ids in this format ‘323233’, ‘323233’, ‘34433’. The problem is that if the list has more than 1000 students it throws a error because of the in ( & StudentList & ) part.
I’m trying to figure out this problem but can’t think of a solution. The code is vb.net and sql. Any ideas how to solve this problem or alternative ways to approach it.

select ' Please Select...' classDets  , '' ivalue 

from dual

 union

 SELECT distinct t1.m_reference || ' - ' || t1.m_name classDets ,t1.m_reference ivalue  

  FROM u.c_person t2, u.c_module t1, u.c_moduleenrolment t3

 WHERE (t3.e_student = t2.p_id) 

 AND (t3.e_module = t1.m_id)

 and t1.m_type = 'CL'

 and t2.p_id in ( & StudentList & )

 

Code (markup):

 

What datatype do you define StudentList as? Perhaps more than 1000 ids goes past the bit limit for that datatype. Another possible issue is your max_allowed_packet setting. This determines the maximum allowable number of values. But if that is the case you should get a packet size too large error.

What error do you get?

 

I don't have the edzact error type in front of me at the moment. But it's basically saying that you cant have more than 1000 in the in statement. I am looking for a way to change the query so that it can work when it has more than 1000 students in the list.

 

It depends on what the error is.

If the error is that the StudentList variable is exceeding its own size limit then when you define StudentList you either need to specifiy a longer character length or use a different datatype.

If the error is that you are exceeding the maximum packet size then that is a global variable you need to change. To change the max_allowed_packet setting:

1) change the my.ini file to
# sets max_packet to 500 MB
max_allowed_packet=500M

(You can set it smaller if you want, the default is 1MB I think.) OR

2) execute
mysql> set global max_allowed_packet = 500 * 1024 * 1024;
from the command prompt. (Change the 500 to whatever value you want to).

 

it's an oracle error therefore I don't think its affected by packet sizes.

 

You're right. In Oracle there is a 1000 literal limit to the IN clause. From what I understand, there is no limit to the number of values returned in a select statement in the IN clause. You could try getting those ids in the StudentList variable into a table and then doing:

...where t2.p_id in (select studentID from tmpTblStudentIDs)...

Another option is to break the StudentList up into chunks and doing:

...where t2.p_id in ( & StudentList1 & )
or t2.p_id in ( & StudentList2 & )...

but that will probably slow the query a lot.

Did you generate StudentList from a query? If so, try

...where t2.p_id in (sql query you used to generate StudentList)...

If this is a query that will run often, you may want to check the performance against using the exist statement:
(first create a table with the values you want in the list tmpStudentList)

...where exists (select id from tmpStudentList where tmpStudentList.p_id = t2.p_id)...

 

Thanks for the help, it's actually a webservice therefore I can't put it into a table. The studentlist is passed from one server to another. It's probably best to try and split it up unless there is another way to do it?

 

Do you have control of both ends of the webservice (or at least the end where the query is run)?

If you do, then you just need to do some extra coding to take the (I assume XML because it is a webservice) StudentList file and parse it into a temporary table. If you only have control over the end the file is uploaded from, then there is not much you can do except break it up into smaller chunks.

 

You are getting it as a comma separated values and so all you need is a small function to convert it from a list into a table be it a temp table or a table variable and then inner join it in the query thus removing the "in" part of the statement.