At 12:30 AM 19/01/2008, Martijn wrote:

>> The following query works on dialect 1 but fails on dialect 3 with the
>> error: Invalid command. Data type unknown.
>>
>> select
>> mytable1.myfield1,
>> sum(mytable1.myfield2),
>> 0.00 /* dummy to satisfy union */
>
>You probably have to cast this value to a datatype, eg:
>CAST(0.00 AS NUMERIC(18, 4)), depending on the
>result type of SUM(mytable2.myfield2) in the below
>SELECT clause. The same for the second 0.00 occurence.
>
>> from mytable1
>> group by
>> mytable1.myfield1
>> union
>> select
>> mytable2.myfield1,
>> 0.00, /* dummy to satisfy union */
>> sum(mytable2.myfield2)
>> from mytable2
>> group by
>> mytable2.myfield1

Actually, only the constant in the first set of the union needs to be cast. In Firebird 1.5 the data type and column name of each selected field determines the data type and column name for the whole result set. In Fb 2, unions are smarter about ascertaining the data type and you would probably be able to use it without the cast.

It's a good idea to give the constant field and the aggregate field in the first set some useful alias names.

./heLen