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

>
> In unions, the first SELECT determines the structure of the

output, so all

> all following SELECTs must return rows that are congruent with

first one

> and matching in data type AND size. (Think of the first SELECT as

a sort

> of "mini view definition" :-] )
>
> Hence, if you had put the 'Average' query first, you would have

got an

> exception on the second query.
>
> As you've discovered, trying to push the result of AVG() into a

field that

> has already been defined as SMALLINT caused an exception because

AVG()

> doesn't return a SMALLINT.
>
> Also, the output column names are determined by the first select.

Make it

> a rule always in SQL to assign identifiers to derived fields (i.e.

those

> that are calculated from expressions). Not doing so doesn't

usually cause

> exceptions on the server, since the engine will assign its own

(weird)

> names; but it sure can stuff up applications, since the app has

no idea

> what it's looking for when referring to the set's column

identifiers.

>
> Firebird 2 has a lot of improvements with regard to unions,

particularly in

> the area of data coercion. It will actually scan all of the

internal sets

> and, if it can, will coerce a column to the largest compatible

type,

> regardless of the set's position in the union.
>
> Despite that, IMO, there are plenty of good reasons to coerce the

types

> yourself so as to write maintainable code and avoid surprises.
>
> ./heLen
>

Thanks for the details on the inner workings of Firebird Helen. Take
your (and Adam's) point that I should have 'coerced' the data types
myself rather than to 'leave it to chance' - even with the 'Improved
State of the Union' in FB 2! :)

Rgds,
Tony