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 »

  1. hello!This was a really quality theme!
    I come from milan, I was luck to approach your website in yahoo
    Also I get much in your Topics really thanks very much i will come every day

    Comment by bet365 | October 3, 2010 | Reply

  2. If possible, don’t use “IN” with this many items. Use a JOIN instead.

    select searchTable.pa_num, searchTable.bill_num, …
    from searchTable
    inner join
    (
    select pat_num from tablename where
    ) tKeyList on tKeyList.pa_num = searchTable.pa_num

    Comment by Paul | October 12, 2010 | Reply

    • Paul,

      How does JOIN resolve this issue?

      Ex:
      SELECT * FROM InvoiceList WHERE Invoice_No IN (1…1000) OR Invoice_No (1001…2000)

      How do you modify this just by using JOIN (without IN)?

      Comment by Ramesh | October 26, 2010 | Reply

    • Paul,
      that is only true if you can build another query to select all the elements that are in the ‘IN’ clause. If you can not (I know it may be a rare case – but it can happen: lets assume the numbers are randomly chosen by the user and user really wants them) then the solution provided by JG Vimalan just works.

      Comment by bartoszrybacki | January 24, 2012 | Reply

  3. thank you… ur post helped me.

    Comment by rem | November 13, 2010 | Reply

  4. thanks dude!

    Comment by helios | November 28, 2010 | Reply

  5. Thanks!

    Comment by mo | February 21, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: