Ok, I had error on my post, I can confirm that Oracle does *not*
support the syntax with the aggregate function we've been discussing:

SQL> CREATE VIEW FLASH_V3 AS SELECT AVG(X) FROM FLASH_T1;
CREATE VIEW FLASH_V3 AS SELECT AVG(X) FROM FLASH_T1
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias

My bad, I had confused it with this other related view definition,
which Oracle supports and Firebird does not.

SQL> CREATE VIEW FLASH_V2 AS SELECT T1_T.X FROM (SELECT * FROM
FLASH_T1) T1_T, FLASH_V1 WHERE T1_T.X =FLASH_V1.X;

View created.



To summarize, here are Firebird Views V2 and V3 that fail with
error, "must specify column name for view select expression".

CREATE TABLE T1( X INTEGER);
CREATE TABLE T2 (X INTEGER, Y INTEGER);
CREATE VIEW V1 AS SELECT X, Y FROM T2;

CREATE VIEW V2 AS SELECT T1_T.X FROM (SELECT * FROM T1) T1_T, V1
WHERE T1_T.X=V1.X ;

CREATE VIEW V3 AS SELECT AVG(X) FROM T1 ;




Here is the entire Oracle log, showing how the view with the subquery
works.


Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> CREATE TABLE FLASH_T1 (X int);

Table created.

SQL> insert into FLASH_T1 VALUES(42);

1 row created.

SQL> CREATE TABLE FLASH_T2 (X int, Y int);

Table created.

SQL> INSERT INTO FLASH_T2 VALUES(42, 142);

1 row created.

SQL> CREATE VIEW FLASH_V1 AS SELECT X, Y FROM FLASH_T2;

View created.

SQL> CREATE VIEW FLASH_V2 AS SELECT T1_T.X FROM (SELECT * FROM
FLASH_T1) T1_T, FLASH_V1 WHERE T1_T.X
=FLASH_V1.X;

View created.

SQL> CREATE VIEW FLASH_V3 AS SELECT AVG(X) FROM FLASH_T1;
CREATE VIEW FLASH_V3 AS SELECT AVG(X) FROM FLASH_T1
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias


SQL> CREATE VIEW FLASH_V3 AS SELECT AVG(X) as avergage FROM FLASH_T1;

View created.

SQL> SELECT * FROM FLASH_V1;

X Y
---------- ----------
42 142

SQL> SELECT * FROM FLASH_V2;

X
----------
42

SQL> SELECT * FROM FLASH_V3;

AVERGAGE
----------
42