OraFAQ Forum: SQL & PL/SQL » ad hoc MINUS - compare values in SQL code to values in table

Home » SQL & PL/SQL » SQL & PL/SQL » ad hoc MINUS - compare values in SQL code to values in table (Oracle 10g)

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

ad hoc MINUS - compare values in SQL code to values in table [message #599733]

Mon, 28 October 2013 07:28

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Hi

I am searching the simplest way for ad hoc MINUS.

I do:

SELECT *
  FROM uam_rss_user_XXXXXXX
 WHERE host_name IN
          ('XXX0349',
           'XXX0362',
           'XXX0363',
           'XXX0343',
           'XXX0342',
           'XXX0499',
           'XXX0500',
           'XXX0332',
           'XXX0364',
           'XXX0505',
           'XXX0487',
           'XXX0486',
           'XXX0336',
           'XXX0337')

and look in the table which values are missing (values that are in host_name IN but not in actual table).

is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. Is there a faster solution?

How are experienced Oracle pros doing this task?

Attachment: adhoc_minus.JPG
(Size: 80.43KB, Downloaded 537 times)

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599738 is a reply to message #599733]

Mon, 28 October 2013 07:41

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

Senior Member

what exactly is ad-hoc here?

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599739 is a reply to message #599733]

Mon, 28 October 2013 07:45

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Hint:

SQL> select * from table(sys.odcivarchar2list('XXX0349',
  2             'XXX0362',
  3             'XXX0363',
  4             'XXX0343',
  5             'XXX0342',
  6             'XXX0499',
  7             'XXX0500',
  8             'XXX0332',
  9             'XXX0364',
 10             'XXX0505',
 11             'XXX0487',
 12             'XXX0486',
 13             'XXX0336',
 14             'XXX0337'));
COLUMN_VALUE
-------------------------------------------------------------
XXX0349
XXX0362
XXX0363
XXX0343
XXX0342
XXX0499
XXX0500
XXX0332
XXX0364
XXX0505
XXX0487
XXX0486
XXX0336
XXX0337

14 rows selected.

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599741 is a reply to message #599739]

Mon, 28 October 2013 08:12

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

thanks. I really need to learn PL/SQL. I guess I can't avoid it in Oracle world..

SELECT *
  FROM TABLE (SYS.odcivarchar2list ('GGGGGGGGG349',
                                    'GGGGGGGGG362',
                                    'GGGGGGGGG363',
                                    'GGGGGGGGG343',
                                    'GGGGGGGGG342',
                                    'GGGGGGGGG499',
                                    'GGGGGGGGG500',
                                    'GGGGGGGGG332',
                                    'GGGGGGGGG364',
                                    'GGGGGGGGG505',
                                    'GGGGGGGGG487',
                                    'GGGGGGGGG486',
                                    'GGGGGGGGG336',
                                    'GGGGGGGGG337'))
MINUS
SELECT host_name
  FROM uam_rss_user_GGGGGGGGG
 WHERE host_name IN
          ('GGGGGGGGG349',
           'GGGGGGGGG362',
           'GGGGGGGGG363',
           'GGGGGGGGG343',
           'GGGGGGGGG342',
           'GGGGGGGGG499',
           'GGGGGGGGG500',
           'GGGGGGGGG332',
           'GGGGGGGGG364',
           'GGGGGGGGG505',
           'GGGGGGGGG487',
           'GGGGGGGGG486',
           'GGGGGGGGG336',
           'GGGGGGGGG337')

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599742 is a reply to message #599738]

Mon, 28 October 2013 08:14

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

cookiemonster wrote on Mon, 28 October 2013 07:41

what exactly is ad-hoc here?

did I use the term "ad hoc" wrong?

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599743 is a reply to message #599742]

Mon, 28 October 2013 08:38

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

Senior Member

Nope, Michels answer makes it clearer to me.

You realise your 2nd query uses no PL/SQL?

Report message to a moderator

Re: ad hoc MINUS - compare values in SQL code to values in table [message #599749 is a reply to message #599743]

Mon, 28 October 2013 09:57

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

cookiemonster wrote on Mon, 28 October 2013 08:38

Nope, Michels answer makes it clearer to me.

You realise your 2nd query uses no PL/SQL?

Yes sure I know this. I have zero PL/SQL know how, but passed SQL Fundamentals 11g exam. So not zero SQL Laughing

Report message to a moderator

Previous Topic:	SQL IN FUNCTION
Next Topic:	XMLTYPE datatypw

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 06:55:36 CDT 2024