SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset 
Author Message
 SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset

hi,

the following statement should return only rows returned by the first query
but not by the second:

SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT WHERE TRIM(LOWER(TT.EMAIL)) NOT
IN

(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

The resultset of this query is empty!!!

Exactly the same two queries combined with MINUS:

SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT

MINUS

(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

...now it returns 752 rows which are only in the first query!

what iam doing wrong? i tried everything!

thanks...

andre



Tue, 23 Mar 2004 21:50:55 GMT
 SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset


Quote:

>hi,

>the following statement should return only rows returned by the first query
>but not by the second:

>SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT WHERE TRIM(LOWER(TT.EMAIL)) NOT
>IN

>(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
>KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

>The resultset of this query is empty!!!

you've got nulls in the subquery i'll bet.

consider:


Table created.


Table created.



1 row created.


1 row created.



1 row created.



from t1 );

         X
----------
         2


1 row created.


from t1 );

no rows selected


from t1 where x is NOT NULL );

         X
----------
         2

Quote:
>Exactly the same two queries combined with MINUS:

>SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT

>MINUS

>(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
>KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

>...now it returns 752 rows which are only in the first query!

>what iam doing wrong? i tried everything!

>thanks...

>andre

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp


Wed, 24 Mar 2004 00:50:24 GMT
 SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset


Quote:
> hi,

> the following statement should return only rows returned by the first
query
> but not by the second:

> SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT WHERE TRIM(LOWER(TT.EMAIL)) NOT
> IN

> (SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
> KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

> The resultset of this query is empty!!!

> Exactly the same two queries combined with MINUS:

> SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT

> MINUS

> (SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
> KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)

> ...now it returns 752 rows which are only in the first query!

> what iam doing wrong? i tried everything!

> thanks...

> andre

The distinct in the subquery in the first solution is redundant and should
be deleted. The parameter to in is a set and sets are unique by design.
Oracle already will perform a distinct for you and not leave your distinct
out.
The column alias in the subquery is also redundant.
The distincts in the minus scenario are alos redundant (all of them) as
MINUS is a set operator.
The use of TT.EMAIL is inconsistent.
You trim it in the first solution and you don't trim it in the second
solution.

Hth,

Sybrand Bakker, Senior Oracle DBA



Wed, 24 Mar 2004 01:18:58 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. ??? Subsets of a select without re-selecting ???

2. Select into SQL Server without Not Null Columns

3. select * from A where pk1,pk2 not in (select pk1,pk2 from B)

4. How can I using the Select statement to not selecting the Chinese Characters

5. HELP:select all columns, but do not select where specific columns are duplicated

6. Help with Select not in (select...)

7. ** Select (Select ) not possible !!??

8. ??Select (Select) from not possible???!!!

9. How to return resultset from sp to select (SQL 7)

10. Exec Stored Proc for Select ResultSet task

11. Select from resultset returned by a stored procedure

12. Sorting a resultset within the same SELECT


 
Powered by phpBB® Forum Software