Hi Ismael!

I don't quite understand your question, but I expect the equivalent SQL with EXISTS to be:

select aftent.aft, aftent.desaft as descripcion, aftent.valorusd, aftent.valormn, aftent.aft || aftent.desaft as search
from aftent
where aftent.entidad = 'unidad1'
and not exists(select * from afttmp where aftent.aft = afttmp.aft and afttmp.entidad = 'unidad1')
order by aftent.aft

If aftent.entidad or afttmp.entidad can be NULL, the queries will return slightly different result sets. If that is a problem, try to add
'and aftent.entidad is not null' to your main query and possibly 'afttmp.entidad is not null' to your subquery (I'm not certain whether the subselect to the subquery is needed, nor whether you should use AND or OR - too little experience on my part with IN (<subselect>)).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ismael L. Donis García
Sent: 16. september 2009 06:11
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] About EXISTS predicate

Excuse the question but not yet I understand EXISTS syntax.

As the following SQL would remain?

select aftent.aft, aftent.desaft as descripcion, aftent.valorusd, aftent.valormn, aftent.aft || aftent.desaft as search from aftent where (((aftent.entidad)='unidad1') and aftent.aft not in (select afttmp.aft from afttmp where (((afttmp.entidad)='unidad1')))) order by aftent.aft



Regards
=========
¦¦ ISMAEL ¦¦
=========
"Oír o leer sin reflexionar es una ocupación inútil".
Confucio (551-479 a. C.); filósofo chino.

----- Mensaje original -----
De: Helen Borrie
Para: firebird-support@yahoogroups.com
Enviado: miércoles, 16 de septiembre de 2009 06:46
Asunto: Re: [firebird-support] About EXISTS predicate

>> Just one simple doubt, Is there any limitation for EXISTS predicate too, as
>> we have for IN predicate for 1500 values ?
>

At 09:22 PM 16/09/2009, Thomas Steinmaurer wrote:

>No, because EXISTS is a different construct than IN, which does not
>expect a list of input values.

There is more to it, as well. ;-)

The 1499 limit applies to an IN() predicate of the form

IN (const1, const2, ....)

The limit doesn't apply when the argument is a subquery.

Note, too:

The engine actually resolves the subquery form to EXISTS(), because it is logically equivalent and is a far more economical search than IN(). However, this does *NOT* apply to a NOT IN() predicate. While NOT IN(sq) AND NOT EXISTS() are equivalent in many situations, there are some situations where they return different results. (I can't think of an example; I just know that it is proven somewhere that this can be so.)

./heLen





[Non-text portions of this message have been removed]



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links