Re: urgent little help
>From: uunet!ghost.dsi.unimi.it!ruggiero (francesco ruggiero)
>Subject: urgent little help
>Date: Wed, 16 Jun 1993 14:09:28 GMT
>X-Informix-List-Id: <news.3581>
>select distinct genere
>from libri
>where genere not matches
> (select specializzato_in.genere
> from specializzato_in , libri
> where libri.genere matches specializzato_in.genere
> and libri.cod_fisc_redat = specializzato_in.cod_fiscale)>escape "."
>
> 284: A subquery has returned not exactly one row.
Maybe:
SELECT DISTINCT genere
FROM libri
WHERE genere NOT MATCHES ALL
(SELECT specializzato_in.genere
FROM specializzato_in, libri
WHERE libri.genere MATCHES specializzato_in.genere
AND libri.cod_fisc_redat = specializzato_in.cod_fiscale)ESCAPE "."
I'm not certain I understand what you are trying to do, but I think you
are trying to find entries in Libri for which there is no corresponding
entry in Specializzato_in? If this is the case, then you should
probably be using:
SELECT DISTINCT genere
FROM libri
WHERE genere NOT IN
(SELECT specializzato_in.genere
FROM specializzato_in, libri
WHERE libri.genere = specializzato_in.genere
AND libri.cod_fisc_redat = specializzato_in.cod_fiscale)
Note that I've changed both MATCHES. I can't think of a situation where
the IN would need to be replaced by MATCHES, nor am I convinced that I
understand why the second MATCHES needs to be MATCHES rather than '='.
Bear in mind that MATCHES is much slower than '='; much, much slower
when the MATCHES "string" is a variable as in the example you supplied.
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>