The list of columns in the view is now optional for most views, but before 2.5 it is required if the view involves UNION.

See http://tracker.firebirdsql.org/browse/CORE-1402

--- In firebird-support@yahoogroups.com, "modaoau" <modaoau@...> wrote:
>
> Hi;
>
> I am using Firebird 2.1.3. The documentation leads me to believe that UNION queries in CREATE VIEW DDL statements are fully supported in FB 2.0+.
>
> I can successfully run the following query as an SQL SELECT statement:
>
> SELECT DISTINCT NULL AS TABLE_CATALOG , NULL AS TABLE_SCHEMA ,RDB$RELATION_NAME AS TABLE_NAME, 'BASE TABLE' AS TABLE_TYPE
> FROM RDB$RELATION_FIELDS
> WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NULL
> UNION
> SELECT DISTINCT NULL AS TABLE_CATALOG , NULL AS TABLE_SCHEMA ,RDB$RELATION_NAME AS TABLE_NAME, 'VIEW' AS TABLE_TYPE
> FROM RDB$RELATION_FIELDS
> WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NOT NULL;
>
> However;
>
> When I try to create a view using the following syntax:
>
> CREATE VIEW INFORMATION_SCHEMA_TABLES AS
> SELECT DISTINCT NULL AS TABLE_CATALOG , NULL AS TABLE_SCHEMA ,RDB$RELATION_NAME AS TABLE_NAME, 'BASE TABLE' AS TABLE_TYPE
> FROM RDB$RELATION_FIELDS
> WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NULL
> UNION
> SELECT DISTINCT NULL AS TABLE_CATALOG , NULL AS TABLE_SCHEMA ,RDB$RELATION_NAME AS TABLE_NAME, 'VIEW' AS TABLE_TYPE
> FROM RDB$RELATION_FIELDS
> WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NOT NULL;
>
> The view will fail with the following error:
>
> SQL Error.
> SQL error code = -607.
> Invalid command.
> must specify column name for view select expression.
>
> Interestingly, when I create the view using the syntax upto and immediately before the UNION statement, it works correctly.
>
> Am I missing something, or is there an issue regarding the use of the UNION keyword in the CREATE VIEW statement?
>
> Any help would be greatly apprecaited, as this is driving me nuts.
>
> Kindest Regards
>
>
> Dave Ballantyne
>