Jonathan Gennick has an excellent article in "Oracle" magazine (sept./oct.), which should help. He demonstrates two approaches: with and without pivot table.
Igor Neyman, OCP DBA [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002 1:28 PM > 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----- > Sent: Monday, September 23, 2002 10:28 AM > To: Multiple recipients of list ORACLE-L > > > 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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).