Home » SQL & PL/SQL » SQL & PL/SQL » Count( ) (Generic)
Count( ) [message #407177] Mon, 08 June 2009 23:45 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
How do i get count from a MINUS statement.


select count(table_name) from dba_tables where owner='SCOTT'
minus 
select count(table_name) from dba_capture_prepared_tables where table_owner='SCOTT'


above statement gives count same as select count(table_name) from dba_tables where owner='SCOTT' .

Need help .Thanks.


Re: Count( ) [message #407179 is a reply to message #407177] Mon, 08 June 2009 23:51 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
MINUS is a SET operator not an arithmetic operator. It does not subtracts the value of the result set. You need something like this

 SELECT (select count(table_name) from dba_tables where owner='SCOTT') -
	(select count(table_name) from dba_capture_prepared_tables where table_owner='SCOTT') val
   FROM dual	

[Updated on: Mon, 08 June 2009 23:52]

Report message to a moderator

Re: Count( ) [message #407180 is a reply to message #407177] Mon, 08 June 2009 23:56 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Karthi,Thank you. It was a good learning. 'MINUS is just a set operator' .
Re: Count( ) [message #407226 is a reply to message #407180] Tue, 09 June 2009 03:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As is is a Set operator, you can just do a count on the rows it returns:
select count(*)
from  (select table_name from dba_tables where owner='SCOTT'
      minus 
       select table_name from dba_capture_prepared_tables where table_owner='SCOTT');
Previous Topic: Formatting using UTL_FILE package
Next Topic: array version of dbms_sql
Goto Forum:
  


Current Time: Tue Dec 06 14:27:31 CST 2016

Total time taken to generate the page: 0.10358 seconds