Andreas Zeugswetter wrote:
>
> >Also, could someone test is HAVING without aggregates
> >disallowed or not:
> >
> >select a, min (b) from x group by a having a = 0;
>
> allowed in Informix:
> a (min)
> No rows found.
Thanks, Andreas!
I'll comment out some code... Actually, non-aggregate expressions
could be moved to WHERE, but at the moment I'll just allow them in
HAVING.
Ok, there are also some problems with subselects in HAVING
but I haven't time to fix them now:
select a as a2, b as b2, c as c2 into table x2 from x;
select a, sum(b) from x group by a having avg(c) =
(select max(c2) from x2 where a2 = a/2);
-- ok
select a/2, sum(b) from x group by a/2 having avg(c) =
(select max(c2) from x2 where a2 = a/2);
-- ERROR: You must group by the attribute used from outside!
-- this means that GroupBy func doesn't work here...
select a, sum(b) from x group by a having avg(c) =
(select max(c2) from x2 where a2 = max(b));
-- ERROR: parser: aggregates not allowed in WHERE clause
-- Is this allowed in another dbms-es ???
-- This is not problem of HAVING but subselects...
select a as f, sum(b) from x group by f having avg(c) =
(select max(c2) from x2 where a2 = f);
-- ERROR: attribute 'f' not found
-- Should be aliasing handled in subselects ???
Vadim