Once again I'll recommend using EXISTS:

ANZAHL = (SELECT COUNT (*) FROM MITARBVIEW MA
WHERE NOT EXISTS(SELECT 1 FROM MITARBEINAUSVIEW EA
WHERE 'TODAY' >= EA.EINTRITTSDATUM
AND EA.MITARBID = MA.PERSONENIDINT AND (EA.AUSTRITTSDATUM IS NULL
OR EA.AUSTRITTSDATUM >= 'TODAY'));

Though I must admit that I don't understand your error, so as far as I
know, you may still get problems.

HTH,
Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation


At 12:47 10.02.2003 +0100, you wrote:

>Am Sonntag, 9. Februar 2003 23:35 schrieb Aage Johansen:
> > Show us the statement where you do the count.
>
>I use Kylix 2 and dbExpress. Opening the SQLDataSet with the postet select
>statement is ok. There is for the DataSet a property RecordCount. If I read
>this property comes the error message. So I thought it is a problem of the
>dbExpress driver and tried it with a SP:
> ANZAHL = (SELECT COUNT (*) FROM MITARBVIEW MA
> WHERE 'TODAY' >= (SELECT MAX(EA.EINTRITTSDATUM) FROM
> MITARBEINAUSVIEW EA
> WHERE ((EA.MITARBID = MA.PERSONENIDINT) AND ((EA.AUSTRITTSDATUM IS
>NULL) OR (EA.AUSTRITTSDATUM >= 'TODAY')))));
> SUSPEND;
>Now I recieve error -206 "subselect is illegal in this context". How can I
>count, if subselect is illegal? The task is:
>Table 1 contains the employees and table 2 the starting and ending dates for
>all employees. But for 1 employee can exist many starting and ending dates.
>How can I know, how many employees are working now?