> 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
>

hmm, no I should have been clearer, it's actually a sub query. the whole
thing is:
SELECT ID
, BATCHCOUNT
, (SELECT COUNT(*) FROM ERSINVOICE WHERE ERSINVOICE.FKBATCH IN
(SELECT ERSVOUCHERBATCH.ID FROM ERSVOUCHERBATCH WHERE
ERSVOUCHERBATCH.FKEXPORTLOG=ERSBATCHESLOG.ID))
, BTYPE
FROM ERSBATCHESLOG

so the identifiers are correct, but it might be a double EXISTS clause.
BTW this runs very fast in 1.5 but it's a dog in 2.1
Alan