> >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
>
> It's a subquery expression with an embedded existence subquery.
>
>
> >so the identifiers are correct,
>
> No. And Fb 2 needs them to be.
>
> >but it might be a double EXISTS clause.
>
> It's not.
>
> >BTW this runs very fast in 1.5 but it's a dog in 2.1
>
> It probably gives wrong results in 1.5 as well.
>
> SELECT
> ebl.ID,
> ebl.BATCHCOUNT,
> (SELECT COUNT(inv.*) FROM ERSINVOICE inv
> WHERE inv.FKBATCH IN
> (SELECT evb.BATCH.ID FROM ERSVOUCHERBATCH evb
> WHERE evb.FKEXPORTLOG=ebl.ID)) as InvCount,
> ebl.BTYPE
> FROM ERSBATCHESLOG ebl
>
> But I'm pretty sure you'll get that count a lot faster by replacing the
> embedded existence query with an inner join, thus removing the second
> level of correlation:
>
> SELECT
> ebl.ID,
> ebl.BATCHCOUNT,
> (SELECT COUNT(inv.*) FROM ERSINVOICE inv
> join ERSVOUCHERBATCH evb
> on evb.BATCH.ID = inv.FKBATCH
> WHERE evb.FKEXPORTLOG=ebl.ID) as InvCount,
> ebl.BTYPE
> FROM ERSBATCHESLOG ebl
>
> ./h

thanks all - this seems to do th trick Helen,
Interesting though that my FB2.1 doesn't like the COUNT(INV.*) syntax
Alan