At 09:58 PM 22/02/2008, Milan wrote:

>Uwe Grauer wrote:
>> In the query
>> "SELECT E_TOK, E_VAL, count(*) as ent_count
>> FROM ENUM
>> group by E_TOK, E_VAL
>> having ent_count > 1"
>> i get this:
>> Dynamic SQL Error
>> SQL error code =-206
>> Column unknown
>> ENT_COUNT
>>
>> Using "having count(*) > 1" doesn't give an error.
>> Can someone please explain the reason for this?
>
>Column aliases are only used to output a column name. However, Firebird
>does seem inconsistent here:
>
>- You cannot use column alias in HAVING and WHERE:
>
>select emp_no x
>from employee
>where x > 10;
>
>- But you can in GROUP BY and ORDER BY:
>
>select emp_no x, max(hire_date) y
>from employee
>group by x
>order by y;
>
>I tested this with 2.0.3. Maybe some of this stuff is improved in 2.1.

No, it's not "inconsistent". GROUP BY operates on the logical set that is output from the columns specification, and produces a *physical* set which includes the results of aggregations as scalars. The HAVING clause operates on that physical set. To be able to "refer back" to the expression from which those scalars derived, the engine has to repeat the original aggregation row by row and compare the result with the value in the output.

For Uwe's benefit, Ismael's point is that the expression in the HAVING clause must be syntactically correct. There is no such expression syntax as "having count > 1". As Ismael pointed out, it should be "having count(*) > 1".

./hb