Re: HELP !!! SQL GURUS !!!! How do I do this ???

jens_at_deutschware.com (Jens U. Veigel) wrote:

>I was trying somthing like this:
>
>select column
>from table
>where column is not in
>
>(select column from
>table
>where column is in ('1','2','3','4','5','6','7','8','9',10')
>
>
>then it returns 1,2,3,4,5
>
>what I need it to return is 6.7,8,9,10
>(the exact opposite)

Since 6,7,8,9,10 aren't in your table, your select statement won't be able to return them. You can't select something that's not there. My best suggestion would be to create a table with the numbers 1 through 10 and then select from it. Let's say we call this new table the "constant_table":

	select column from constant_table A
	 where not exists (select column 
                             from table B
                            where A.column = B.column);

Hey! Here's a real sleazy<g> approach that just hit me. Try this:

	select X from 
	 (select 1 X from dual
          union select 2 X from dual
          union select 3 X from dual
          union select 4 X from dual
          union select 5 X from dual
          union select 6 X from dual
          union select 7 X from dual
          union select 8 X from dual
          union select 9 X from dual
	  union select 10 X from dual)
         where X not in ( select x from y);

With table y looking like this:

SQL> describe y

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        NUMBER(38)

SQL> select * from y
  2 ;

        X

        1
        2
        3
        4
        5

It works for me. Here's what I get from SQL*PLUS using Oracle 7.3:

SQL> select X from
  2 (select 1 X from dual

  3            union select 2 X from dual
  4            union select 3 X from dual
  5            union select 4 X from dual
  6            union select 5 X from dual
  7            union select 6 X from dual
  8            union select 7 X from dual
  9            union select 8 X from dual
 10            union select 9 X from dual
 11     union select 10 X from dual)
 12           where X not in ( select x from y);

        X
---------
        6
        7
        8
        9
       10

Isn't that cool! I'm pumped that I even thought of it. If your table (y in my case) is large, you might want to rework that "not in" predicate and turn it into a "not exists" predicate.

Wow! Hope this helps! Let me know.

regards,

Jonathan Received on Tue Apr 01 1997 - 00:00:00 CST