--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> 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
>

Hi Martijn & Helen

Just to round up the thread, I had to put explicit casts on everything
to get this to work [on FB 1.5.4], casting just the first query still
resulted in the same error. However, aliasing just the first query
works ok.

Thanks for your help on this. Here is the final working query:

select
mytable1.myfield1 mygroup,
cast(sum(mytable1.myfield2) as numeric(18,4)) mysum1,
cast(0 as numeric(18,4)) mysum2
from mytable1
group by
mytable1.myfield1
union
select
mytable2.myfield1,
cast(0.00 as numeric(18,4)),
cast(sum(mytable2.myfield2) as numeric(18,4))
from mytable2
group by
mytable2.myfield1