Hello Richard,

> I am using Firebird 1.5.4 and converting an existing database
> structure from dialect 1 to dialect 3. All is going well apart from
> one issue with syntax as follows:
>
> Assuming I have 2 tables, mytable1 & mytable2, each with 2 fields as
> follows: myfield1 varchar(10), myfield2 double precision
>
> 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
>
> Can anyone explain why please?
>
> Incidentally, if myfield2 is a decimal type. This query runs ok on
> dialect 3.
>
> Thanks in advance for any help.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com