OraFAQ Forum: SQL & PL/SQL » SQL query help

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query help [message #647836]

Tue, 09 February 2016 18:40

nsankineni
Messages: 1
Registered: February 2016

Junior Member

Would you please check the query below and let me know the other alternatives if we have any to avoid the MINUS on the query.

I was trying find the users who has 0 objects as well as the below mentioned on the where clause.

select username from dba_users where username not in ('APPQOSSYS','DBSNMP','DIP','OUTLN','SYSTEM','EDBCON') and username not in (select schema from dba_registry) and username not in ( select grantee from dba_role_privs where granted_role in ('DBA','DSM_ROLE'))
minus select OWNER from all_objects GROUP BY OWNER having count(*) > 0;

It also produces different results when do a count

select count(1) from dba_users where username not in ('APPQOSSYS','DBSNMP','DIP','OUTLN') and username not in (select schema from dba_registry) and username not in ( select grantee from dba_role_privs where granted_role in ('DBA','DSM_ROLE'))
minus select count(1) from all_objects GROUP BY OWNER having count(*) > 0;

Report message to a moderator

Re: SQL query help [message #647839 is a reply to message #647836]

Tue, 09 February 2016 19:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SELECT username 
FROM   dba_users 
WHERE  username NOT IN ( 'APPQOSSYS', 'DBSNMP', 'DIP', 'OUTLN', 
                         'SYSTEM', 'EDBCON' ) 
       AND username NOT IN (SELECT SCHEMA 
                            FROM   dba_registry) 
       AND username NOT IN (SELECT grantee 
                            FROM   dba_role_privs 
                            WHERE  granted_role IN ( 'DBA', 'DSM_ROLE' )) 
MINUS 
SELECT owner 
FROM   all_objects 
GROUP  BY owner 
HAVING Count(*) > 0; 

SELECT Count(1) 
FROM   dba_users 
WHERE  username NOT IN ( 'APPQOSSYS', 'DBSNMP', 'DIP', 'OUTLN' ) 
       AND username NOT IN (SELECT SCHEMA 
                            FROM   dba_registry) 
       AND username NOT IN (SELECT grantee 
                            FROM   dba_role_privs 
                            WHERE  granted_role IN ( 'DBA', 'DSM_ROLE' )) 
MINUS 
SELECT Count(1) 
FROM   all_objects 
GROUP  BY owner 
HAVING Count(*) > 0;

Report message to a moderator

Re: SQL query help [message #647840 is a reply to message #647836]

Tue, 09 February 2016 22:16

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

nsankineni wrote on Tue, 09 February 2016 16:40

Would you please check the query below and let me know the other alternatives if we have any to avoid the MINUS on the query.

I was trying find the users who has 0 objects as well as the below mentioned on the where clause.

select username from dba_users where username not in ('APPQOSSYS','DBSNMP','DIP','OUTLN','SYSTEM','EDBCON') and username not in (select schema from dba_registry) and username not in ( select grantee from dba_role_privs where granted_role in ('DBA','DSM_ROLE'))
minus select OWNER from all_objects GROUP BY OWNER having count(*) > 0;

select username 
from   dba_users 
where  username not in ('APPQOSSYS','DBSNMP','DIP','OUTLN','SYSTEM','EDBCON') 
and    username not in (select schema from dba_registry) 
and    username not in (select grantee from dba_role_privs where granted_role in ('DBA','DSM_ROLE'))
and    username not in (select owner from all_objects);

Quote:

It also produces different results when do a count

select count(1) from dba_users where username not in ('APPQOSSYS','DBSNMP','DIP','OUTLN') and username not in (select schema from dba_registry) and username not in ( select grantee from dba_role_privs where granted_role in ('DBA','DSM_ROLE'))
minus select count(1) from all_objects GROUP BY OWNER having count(*) > 0;

Minus is not the same as -. Minus subtracts rows and - subtracts numbers. If your first result set comes up with a single number as a count and the second result set that you "minus" from that does not have that count, then the remaining row is the one from your first result set. For example, if your first result set contains one row with the number 6 and your second result set contains rows with the numbers 2, 4, and 5, then the row containing 6 minus rows containing 2, 4, and 5 returns the row containing 6.

Report message to a moderator

Re: SQL query help [message #647852 is a reply to message #647840]

Wed, 10 February 2016 02:48

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So what you need to do is wrap the first query in an outer query that does a count of rows returned by the 1st query.

Report message to a moderator

Previous Topic:	Row lock without blocker
Next Topic:	Not Exists vs Left Outer Join with NULL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:10:15 CDT 2024