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

From: Andre Doehn <a.doehn_at_gmx.net>
Date: Fri, 5 Oct 2001 15:50:55 +0200
Message-ID: <9pkdt4$iopmi$1@ID-71421.news.dfncis.de>

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 Received on Fri Oct 05 2001 - 08:50:55 CDT