At 10:11 AM 24/02/2008, Kjell wrote:

>Helen Borrie wrote:
>
>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.

You're forgiven. ;-)

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

That's not an argument. In a UNION you can't perform ORDER BY on the participating sets, only on the final output after the UNION, so in that particular case, ORDER BY can *only* be by degree (number representing the horizontal position of the column in the output set).

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

From Fb 2.0 onwards, it became possible to group by and order by an expression. Along with that it also became possible to refer to the name (identifier) you applied to the output of that expression. There's linguistic confusion here more than anything, I think, in that people are referring to this identifier as "an alias". It is not an alias, it is an identifier in its own right.

So what has happened here is that the engine will now find the *expression* from the identifier, just as previously it was able to find the expression from the degree number. That is, for GROUP BY and ORDER BY processing, the engine now has 3 ways to recapture the expression underlying a derived column, where before it had only 2.

a) by restating the expression
b) by referring to the degree number
and now
c) by referring to the identifier

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

" ...the alias ent_count..." - it is not an alias. It is the identifier referring to the expression. But HAVING (and WHERE) need the actual expression (for slightly different reasons).

Certainly COUNT is a reserved word...it is the reserved symbol for the aggregating function COUNT(). Like most functions, COUNT() requires an argument....so...'HAVING COUNT > 1' is not a legal expression, whereas 'HAVING COUNT(*) > 1' is a legal expression.

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

That *was* Ismael's point. ;-)

./heLen