Home » SQL & PL/SQL » SQL & PL/SQL » Pull out the values which is there in Listing but not there in Table (Oracle 9i)
Pull out the values which is there in Listing but not there in Table [message #431395] Tue, 17 November 2009 04:04 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Hi, I am trying to pull out the values which is there in Listing but not there in Table.
Please find the below query and output. Is the same query can be written in simply way? if yes please provide. Thanks.


select PT
from (
 select '1701' PT from dual union all
 select '1702' from dual union all
 select '1703' from dual)
minus
select pat
from pat_pos
where Study_Id = 1234;

OUTPUT

PT
----------
1702
1703



421701 is present in the Table pat_pos and 421702, 421703 is not there in the Table but there in the Listing.

Re: Pull out the values which is there in Listing but not there in Table [message #431397 is a reply to message #431395] Tue, 17 November 2009 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, "minus" is most often the best way for this case.

Regards
Michel
Re: Pull out the values which is there in Listing but not there in Table [message #431402 is a reply to message #431395] Tue, 17 November 2009 04:21 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
If i am not missing some thing here...

[i miss read it...agree

sriram Wink

Both must have same datatype....

[Updated on: Tue, 17 November 2009 04:23]

Report message to a moderator

Re: Pull out the values which is there in Listing but not there in Table [message #431407 is a reply to message #431402] Tue, 17 November 2009 04:30 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
If my select list crosses more than 100 rows then query will run very slow. What is the solution for this?



select '1701' PT from dual union all
 select '1702' from dual union all
 select '1703' from dual union all
 select '1704' from dual union all
 select '1705' from dual union all
 select '1706' from dual union all
.............................
 select '1999' from dual




Re: Pull out the values which is there in Listing but not there in Table [message #431409 is a reply to message #431407] Tue, 17 November 2009 04:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Minus operator does a distinct and thus may need to spend extra time to do this operation before returning the resultset.

If you are fine with duplicates returned in the data set then try to rewrite the query using "not in" or "not exists" & see if you get better performance
Re: Pull out the values which is there in Listing but not there in Table [message #431417 is a reply to message #431407] Tue, 17 November 2009 05:05 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If my select list crosses more than 100 rows then query will run very slow.

If you think about a row generator click on http://www.orafaq.com/forum/t/95011/102589/

Regards
Michel

[Updated on: Tue, 17 November 2009 05:05]

Report message to a moderator

Previous Topic: exchange partition for moving data into history table
Next Topic: Cobines two queries and using one uniform column
Goto Forum:
  


Current Time: Tue Dec 06 12:25:49 CST 2016

Total time taken to generate the page: 0.09725 seconds