Re: SQL question

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
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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: steven.haas_at_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_at_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: Fink, Dan
> INET: Dan.Fink_at_mdx.com
>
> 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_at_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: Igor Neyman
  INET: ineyman_at_perceptron.com

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_at_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).