Helen Borrie wrote:

>
>
> 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.

Er... I'm rather tired right now, so please excuse me if I'm a bit dense
at the moment. I don't get it.

I understand why ORDER BY can and probably should refer to column
aliases. For one thing, it would have to in the case of union queries,
because in that case the underlying expression might be different in
each "unioned" query.

But Milan said that FB also allows column aliases in GROUP BY. I haven't
tested that he's right, but it DOES seem inconcistent. MS SQL Server
2000 doesn't, by the way. You have to spell out the expression there, in
both WHERE, GROUP BY, and HAVING.

> 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".

I don't get this either. Uwe used alias ent_count for the expression
count(*). In what way does this have anything to do with "having count >
1", where the reserved word(?) count is being used, as opposed to the
alias ent_count?

I don't get Ismael's point either...

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64