JG Vimalan's Blog

It is all about coding!

ORA-01795: maximum number of expressions in a list is 1000

This issue occurs due to limitation in ‘IN’ clause.  The maximum number of parameters allowed is 1000. So, to resolve this issue,  use multiple IN clause with OR. Use 1000 parameters in an IN clause as shown below,

SELECT PAT_NUM, PAT_NAME, STATE FROM PATIENT WHERE  PAT_NUM IN

 (‘1′,’2’…’1000’) OR  PAT_NUM IN (‘1001’, ‘1002’,…) ORDER BY 1 ASC

this will give the expected result.

Advertisements

September 29, 2010 Posted by | Sql Server 2005 | 7 Comments