Home » SQL & PL/SQL » SQL & PL/SQL » How specify more than 1000 values in IN operator
How specify more than 1000 values in IN operator [message #200750] Wed, 01 November 2006 03:42 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

How to specify more than 1000 values in IN operator in SELECT statement,

eg:select * from emp where empid in (3,4,66,74,...upto more than 1000 values)

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

is there any alternative in SQL for above query.

Thanks
Re: How specify more than 1000 values in IN operator [message #200752 is a reply to message #200750] Wed, 01 November 2006 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can get the values into a temporary table, you can use a sub select:
select * 
from   emp 
where empid in (SELECT id from global_temporary_table)
If you can get the values into a userdefined table type (needs to be defined in SQL, not in a package, you can do
SELECT * 
from   emp 
where empid in (SELECT * from table(user_table))
If you can't do any of these, you can use an OR
SELECT * 
from   emp 
where (empid in (1,2,3....1000)
    OR empid in (1001,1002....))
Re: How specify more than 1000 values in IN operator [message #200754 is a reply to message #200750] Wed, 01 November 2006 04:00 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Thank for ur reply..

I think 3rd solution fit to my issue..


Re: How specify more than 1000 values in IN operator [message #200756 is a reply to message #200754] Wed, 01 November 2006 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well... it's the worst of the lot.

If you're using that many values, you'd almose certainly be better off using a global temporary table instead.
Re: How specify more than 1000 values in IN operator [message #200759 is a reply to message #200756] Wed, 01 November 2006 04:09 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
ok,,but how to create global temporary table ...

Thanks
Re: How specify more than 1000 values in IN operator [message #200760 is a reply to message #200759] Wed, 01 November 2006 04:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to add,
JRowbottom's first method using GTT should work very good.
Recently i was asked to look into a performance problem.
The batch job has anywhere between 50,000 to 100,000 values to be used in IN clause and broken into 1000 column sql statements.
We recomended to use GTT and use a table join. Job completed in less than 10 seconds (load the GTT,set statistics for GTT in some cases,do the join ) where it used to be more than 1 hour.

>> ok,,but how to create global temporary table ..
search the forum, read documentation or atleast google yourself.
Re: How specify more than 1000 values in IN operator [message #200797 is a reply to message #200750] Wed, 01 November 2006 06:37 Go to previous message
sreehari
Messages: 101
Registered: May 2006
Senior Member
thanks..all

i could get it..
Previous Topic: SQL output
Next Topic: how I can delete a row
Goto Forum:
  


Current Time: Wed Dec 07 12:55:34 CST 2016

Total time taken to generate the page: 0.09247 seconds