Re: Erroneus Results using the "NOT IN" Clause
Hello Richard!
What's about this:
select unique pa_aufnr FROM patient_plus
where pa_aufnr not in (SELECT unique aufnahmenr FROM ck_key)
(OK. Performance will be low)
Take care.
Dirk Emmermacher
Lowersaxony gymnastics federation
Tel.: +49 511 980 97-34
Fax.: +49 511 980 97-461
Richard Spitz wrote:
>
> Dear Informixers,
>
> a colleague of mine has a problem with the following SQL statement:
>
> select pa_aufnr from t3
> where pa_aufnr not in (select aufnahmenr from t2) ;>
> The result is "no rows found", which is definitely wrong. Both t3 and
> t2 are temp tables generated with:
>
> SELECT unique aufnahmenr FROM ck_key into temp t2;
> SELECT unique pa_aufnr FROM patient_plus into temp t3;>
> Table t2 has 64371 rows, t3 has 61736 rows. There are definitely rows
> in t3 that have no counterpart in t2.
>
> The statement does return correct results when the result set of the
> subquery is reduced, e.g.
>
> select pa_aufnr from t3
> where pa_aufnr not in
> (select aufnahmenr from t2
> where aufnahmenr not like "98%")>
> Is this a known bug? Server version is Online Workgroup Server 7.20UC2
> on the Siemens platform.
>
> Regards, Richard
> --
> +--------------------------+------------------------------------------+
> | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de |
> | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 |
> | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! |
> | 81366 Munich, Germany | GSM : +49-172-8933578 |
> +--------------------------+------------------------------------------+