""alex_vnru"" <ded@...> wrote in message
news:a65on1+ubnh@......

> Hi, All. Having possibility to do Group By UDF() now in FB I was
> surprised we can't to Group By Extract(Year from _timestamp_) and so
> on. Is Extract SDF? If so, what is the principial difference for
> grouping? If it is'nt SDF, what is it?

The only allowed syntax for years was
GROUP BY column_name

column_name can't be a column alias, can't be a number (it only works for
ORDER) and can't be any expression, including built-in functions. Extract(),
cast() and the like are built-in functions. I think that SDF is a name for a
UDF that's compiled with the engine. The native built-in functions aren't
SDF but part of the main code. Furthermore, the SQL standard doesn't give
GROUP BY much freedom:

<group by clause> ::=
GROUP BY <grouping column reference list>

<grouping column reference list> ::=
<grouping colum reference> [ { <comma> <grouping column reference> }... ]

<grouping column reference> ::=
<column reference> [ <collate clause> ]

<column reference> ::= [ <qualifier> <period> ] <column name>

<qualifier> ::= <table name> I <correlation name>

We support the collation for both ORDER and GROUP BY. If you compare with
the standard syntax for sorting:

<order by clause> ::=
ORDER BY <sort specification list>

<sort specification list> ::=
<sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
<sort key> [ <collate clause > ] [ <ordering specification> ]

<sort key> ::= <column name> | <unsigned integer>

you'll see the only a numeric position is allowed for sorting and also, none
of the two clauses allow expressions. Neil McCalden added support for
non-standard grouping by UDF, that's all.

To solve those cases you want, create a view and group from the view.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing