At 10:17 AM 16/05/2005 +0000, you wrote:

>Views will report an error, "Data Type Unknown" if you combine
>multiple statements and do some math on one of the columns.
>
>See the example below.
>
>create table test1 (id varchar(10), qty integer);
>create table test2 (id varchar(10), qty integer);
>
>commit;
>
>insert into test1 (id, qty) VALUES('1', 20);
>insert into test2 (id, qty) VALUES('1', 30);
>
>commit;
>
>create view v_test
>as
> SELECT id, qty FROM test1
>UNION ALL
> SELECT id, qty * -1 FROM test2; <--causes error
>
>commit;
>
>
>Invalid token.Dynamic SQL Error.
>SQL error code = -104.
>Invalid command.
>Data type unknown.
>
>
>If this is a bug, how would it get placed in the Firebird bug list?

No, it's not a bug, it's bona fide exception for a violation of the rules
for creating views. The rule is that, if all of the columns in the view
are database columns, you can use your "lazy" syntax and not specify the
view columns specifically. If you include derived columns in the view,
then you must use the full view specifcation syntax for *all* columns..

But that doesn't fully solve your problem. In a UNION, the engine is
(currently) very conservative in the assumptions it will make about
union-ing a known data type with a expression. The first SELECT statement
sets the data types for the corresponding columns in the succeeding
SELECTs. Thus, the second column must be integer. The engine can't tell
whether the expression in your second SELECT will generate an integer - the
result of multiplication or division might, for example, generate a BigInt,
which is not a 32-bit integer but 64-bit. (Firebird 2 is a bit more clever
about discerning data types in unions...)

So - correct syntax for your view declaration is:

create view v_test (
id, qty)
as
SELECT id, qty FROM test1
UNION ALL
SELECT id, cast(qty * -1 as integer) FROM test2;

./hb