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----- Sent: Monday, September 23, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Dan (and Charlie), Thanks. Good suggestions, but the IN clause contains just over 1700 values. Puzzling, huh? steve --- "Fink, Dan" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Fink, Dan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
