At 10:54 PM 29/07/2008, you wrote:

>This was was my old FB1.5 query:
> SELECT COUNT(*) FROM ERSINVOICE WHERE ERSINVOICE.FKBATCH IN
> (SELECT ERSVOUCHERBATCH.ID FROM ERSVOUCHERBATCH WHERE
>ERSVOUCHERBATCH.FKEXPORTLOG=[!!!!]ERSBATCHESLOG.ID)
>
>can someone throw this into the EXISTS syntax? I'm having a brain explosion
>I'm tryin to get
> THE NUMBER OF INVOICES
> WHERE THE INVOICE BATCH NUMBER
> IS IN THE LIST OF BATCH IDS WHICH ARE IN THE BATCH LOG

SELECT COUNT(*) FROM ERSINVOICE inv
WHERE exists (
(SELECT 1 FROM ERSVOUCHERBATCH batch
WHERE batch.FKEXPORTLOG=inv.ERSBATCHESLOG.ID)

The good news is that, by correcting the identifier syntax in your original query (where [!!!!] is marked), you'll get the engine to run the EXISTS() version automagically.

./h

> regards
> Alan McDonald
>
>
>
>
>
>
>
>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>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
>
>
>