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