Return-path: Envelope-to: oracle-l@orafaq.net Delivery-date: Tue, 24 Sep 2002 01:29:23 +0800 Received: from newsfeed.cts.com ([209.68.248.164]) by jean.onefuse.com with esmtp (Exim 3.36 #1) id 17tX1P-00071I-00 for oracle-l@orafaq.net; Tue, 24 Sep 2002 01:29:23 +0800 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id KAA71905; Mon, 23 Sep 2002 10:29:33 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004D6D2A; Mon, 23 Sep 2002 09:58:31 -0800 Message-ID: Date: Mon, 23 Sep 2002 09:58:31 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Steven Haas Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Steven Haas Subject: RE: SQL question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Dan et al, I guess that is where I may head with it now. I couldn't come up with an easier way. Thanks all... steve --- "Fink, Dan" wrote: > 1700 values? I sure hope you like to type... > > Could you create a text file of the table > values and compare those against a > text files of the possible values? This would > require O/S level privs. > > > -----Original Message----- > From: Steven Haas [mailto:steven.haas@snet.net] > Sent: Monday, September 23, 2002 11:38 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL question > > > Dan (and Charlie), > > Thanks. > Good suggestions, but the IN clause contains > just > over 1700 values. > > Puzzling, huh? > > steve > > > --- "Fink, Dan" wrote: > > It is a little awkward, but a union in an > > inline query may do the trick: > > > > 1 select a.code > > 2 from (select '10' code from dual union > > 3 select '20' code from dual union > > 4 select '30' code from dual union > > 5 select '40' code from dual union > > 6 select '50' code from dual ) a > > 7* where a.code not in (select > > to_char(deptno) from emp) > > SQL> / > > > > CO > > -- > > 40 > > 50 > > > > Dan Fink > > > > -----Original Message----- > > From: Steven Haas > [mailto:steven.haas@snet.net] > > Sent: Monday, September 23, 2002 10:28 AM > > To: Multiple recipients of list ORACLE-L > > Subject: SQL question > > > > > > Good morning list, > > > > Environment HP-UX 11.0 Oracle 8.1.6 > > > > Can anyone help with this SQL. > > > > I can get a result set of values from a table > > that match a given list of values - > > > > select code > > from table > > where code in ('A','B','C','D','E') > > > > I can get a result set of values from a table > > that do not match a given list of values - > > > > select code > > from table > > where code not in ('A','B','C','D','E') > > > > So far so good. > > > > Now, how do I get the set of values from the > > list > > that do NOT have a matching value in the > table? > > > > I cannot create any objects in the schema I > am > > working in otherwise I would create a table > > with > > the values and do a minus, but I can't figure > > out > > how to do it in SQL only. > > > > Thanks in advance, folks. > > > > Steve > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Steven Haas > > INET: steven.haas@snet.net > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > San Diego, California -- Mailing list > > and web hosting services > > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, > send > > an E-Mail message > > to: ListGuru@fatcity.com (note EXACT spelling > > of 'ListGuru') and in > > the message BODY, include a line containing: > > UNSUB ORACLE-L > > (or the name of mailing list you want to be > > removed from). You may > > also send the HELP command for other > > information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Steven Haas > INET: steven.haas@snet.net > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list > and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send > an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling > of 'ListGuru') and in > the message BODY, include a line containing: > UNSUB ORACLE-L > (or the name of mailing list you want to be > removed from). You may > also send the HELP command for other > information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Haas INET: steven.haas@snet.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).