Home » SQL & PL/SQL » SQL & PL/SQL » need help in NOT IN subquery
need help in NOT IN subquery [message #192800] Wed, 13 September 2006 12:08 Go to next message
gopi_ora
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,
I have a problem in a select statement.

i have a select statement which have to fetch records based on some condition like

select name from table where name not in ('a','b','c'...10000)

the values in the where clause 'a','b','c' is more than 10000.
the issue is the values 'a','b' are the real values and they are not from any table.
when i execute the above query am getting the error as ,"argument exceeds" i think we cannot give such a long values in the parathesis.

help me
Re: need help in NOT IN subquery [message #192803 is a reply to message #192800] Wed, 13 September 2006 12:28 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
The maximum number of values is 1000, but I am sure a search of the board will give you the answer. Are you really typing in ten thousand values?
Re: need help in NOT IN subquery [message #192852 is a reply to message #192800] Wed, 13 September 2006 22:28 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
insert all the values (a, b, c,.. 1000) into a GLOBAL TEMPORARY TABLE, then change the select from IN LIST to a subquery - IN (select col from global_temporary_table)
Re: need help in NOT IN subquery [message #192853 is a reply to message #192800] Wed, 13 September 2006 22:36 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
SQL> create table test3 (id number);

Table created.

SQL> begin
  2     for i in 1..10000 loop
  3        insert into test3 values (i);
  4     end loop;
  5     commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     23712

SQL> select *
  2  from test3
  3  where id in (select rownum from all_objects where rownum <= 10000);

And i got the results.


putting a select in the IN clause may solve your limitation of arguments.

Liza
Re: need help in NOT IN subquery [message #192860 is a reply to message #192800] Wed, 13 September 2006 23:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take one step back before searching for a solution for a problem you might not even have.
Where do you get your 10000 literal values from?
Re: need help in NOT IN subquery [message #192887 is a reply to message #192800] Thu, 14 September 2006 01:43 Go to previous messageGo to next message
gopi_ora
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,
Thanks for all for the help.
The first wrong i did is the number of values ,as you all told it is 1000 and not 10000.

The next is am working in a production environment(failed to mention) where i cannot create new tables or any objects.But i need to select some of the records from a table.
help me out.

Thanks.
Re: need help in NOT IN subquery [message #192927 is a reply to message #192800] Thu, 14 September 2006 04:39 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Please read my previous post carefully.

I am using a select on the dictionary view ALL_OBJECTS, you don't need to create any new object, just use this one in the subquery.

If it is a production database then you must have more than 10000 recods in the ALL_OBJECTS dictionary view. and if you do then there is no problem.

Please elloborate your scenerio more if this doesn't solve the problem.

Liza

Re: need help in NOT IN subquery [message #193004 is a reply to message #192927] Thu, 14 September 2006 09:29 Go to previous message
shoblock
Messages: 325
Registered: April 2004
Senior Member
the values are NOT 1 - 1000 NUMERIC. the original question showed ('a','b',...1000), meaning that it would be a LIST OF 1000 NON-NUMERIC VALUES. so how do you propose to use all_objects?

go to your dba's or whoever it is that said "no new tables", and explain that due to limitations of oracle's IN list, combined with user requirements to list over a 1000 possible criteria (I've seen it happen at many client sites), you need to create a new table. tell them that a GLOBAL TEMPORARY TABLE (GTT) will work best. if you aren't familiar with GTT, please read about it in the manuals. briefly, a GTT is a table that is created once - not created and dropped repeatedly - but automatically deletes it's own data, either on commit/rollback or disconnect (as you define at creation time). also, one GTT can be used by multiple sessions at the same time, and each session will only see the data that they themselves have inserted, making it perfect for solving your problem.
Previous Topic: Unique SQL Requirement -- Urgent
Next Topic: Warning Message
Goto Forum:
  


Current Time: Sat Dec 10 12:57:56 CST 2016

Total time taken to generate the page: 0.09190 seconds