It's because you HAVE to name the columns in a view. Of course, if
you simply select columns from a table, the view's columns will be
the table's selected columns.
But in your case you select an aggregate function, which can't be the
name of a column.

So you HAVE to identify the view's column like you did the second
case.

Best regards,
SanTa

--- In firebird-support@yahoogroups.com, "Bill Oliver"
<bill.oliver@...> wrote:

>
> Hi all!
>
> A co-worker sent me this trivial query. Tested against Firebird 2.5,
> but I think this to be present at least in 2.1+
>
> The first CREATE VIEW fails, below, with a specific error message,

so

> clearly we're checking for this case and rejecting it.
>
> My co-worker wonders why we check this case? It seems that MySQL and
> Oracle let you create the view fine. Is this an Firebird
> implementation-specific issue? He feels that (almost) anything valid
> in a select statement would be valid in a view, but that is not the
> case here.
>
> Thanks in advance!
>
> -bill
>
> Here is the script...
> create table t1 (x integer);
> -- straight select works
> select avg(x) from t1;
> -- view gets error
> CREATE VIEW V2 AS SELECT AVG(X) FROM T1;
> -- this view works
> CREATE VIEW V2 AS SELECT AVG(X) as AA FROM T1;
>
>
>
> Here is the log against 2.5.
> SQL> create database 'fb2.5.fdb';
> SQL> show version;
> ISQL Version: WI-T2.5.0.20022 Firebird 2.5 Unstable
> Server version:
> Firebird/x86/Windows NT (access method), version "WI-T2.5.0.20022
> Firebird 2.5 U
> nstable "
> on disk structure version 11.2
> SQL> create table t1 (x integer);
> SQL>
> SQL> -- straight select works
> SQL> select avg(x) from t1;
>
> AVG
> =====================
> <null>
>
> SQL>
> SQL> -- view gets error
> SQL> CREATE VIEW V2 AS SELECT AVG(X) FROM T1;
> Statement failed, SQLSTATE = 42000
> Dynamic SQL Error
> -SQL error code = -607
> -Invalid command
> -must specify column name for view select expression
> SQL>
>
> SQL> CREATE VIEW V2 AS SELECT AVG(X) as AA FROM T1;
> SQL>
>