Re: We are not following the spec for HAVING without GROUP BY

Tom Lane wrote:
> What that means is that neither the HAVING clause nor the targetlist
> can use any ungrouped columns except within aggregate calls; that is,
>
> select col from tab having 2>1
>
> is in fact illegal per SQL spec, because col isn't a grouping column
> (there are no grouping columns in this query).

[...]

> Comments? Can anyone confirm whether DB2 or other databases allow
> ungrouped column references with HAVING?

Oracle does not allow such references. It issues "ORA-00979: not a
GROUP BY expression" when you try to hand it such a reference.

MS SQL Server does not allow such references either, yielding
"columnname is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.".

Can't comment about DB2.

--
Kevin Brown kevin(at)sysexperts(dot)com