Subject Re: How many Unions allowed in firebird Select?
Author Tony Masefield
--- In, Helen Borrie <helebor@t...>
> 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
> 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.
> that are calculated from expressions). Not doing so doesn't
usually cause
> exceptions on the server, since the engine will assign its own
> 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
> 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
> regardless of the set's position in the union.
> Despite that, IMO, there are plenty of good reasons to coerce the
> 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! :)