Hi Svein,

For the not exists, its one of my colleagues who had found this
solution.

With the following query, I get also the error

select e.dept_no, count(*)
from EMPLOYEE e
group by e.dept_no
having count(*) not in (select j.job_grade
from job j)

in fact in having clause, you can put every aggregate function even
if it's not in the select clause, the only constraint is the
existence of the field, example :

select e.dept_no, count(*) from employee e
group by e.dept_no
having max(e.emp_no)<>min(e.emp_no)

you find dept_no with several employee

regards

Alexandre Moradell

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Hi Alexandre!
>
> First, thanks for your mail describing HAVING NOT EXISTS, I've

simply

> never thought of using HAVING this way and learnt something new.
>
> Of course, your queries shouldn't cause any software inconsistency
> errors, but query 3 and 4 seems non-sense to me. You're using HAVING
> MAX ... and HAVING COUNT(*) without them being part of your SELECT.
> The way I read HAVING (I admit I rarely use it), is that it operates
> on the result of the SELECT, and that you cannot access any fields
> from EMPLOYEE that you've not selected. Hence, I'd say Firebird

ought

> to give you an error message at prepare time for these two queries.
>
> Set
>
> --- In firebird-support@yahoogroups.com, "amoradell" wrote:
> > Queries 3 and 4 :
> > select e.dept_no, count(e.emp_no)
> > from EMPLOYEE e
> > group by e.dept_no
> > having count(*) not in (select j.job_grade
> > from job j)
> >
> > select e.dept_no, count(e.emp_no)
> > from EMPLOYEE e
> > group by e.dept_no
> > having max(e.dept_no) not in (select j.job_grade
> > from job j)
> >
> > Result 3 :
> > In firebird.log after execution
> >
> > AMC1 (Server) Wed Feb 22 19:42:30 2006
> > Database: C:\FB153\EXAMPLES\EMPLOYEE.FDB
> > internal gds software consistency check (EVL_expt: invalid
> > operation (232))
>