At 08:00 PM 5/11/2005 +0000, Tony Masefield wrote:

>--- In firebird-support@yahoogroups.com, "Martijn Tonies"
><m.tonies@u...> wrote:
> > Ehm, are you doing the CAST with the 3rd union as well?
> >
> > With regards,
> >
>Hi Martijn,
>
>The problem was with the AVG() - the return data type was not
>compatible with the Min and Max -
>i.e Select 'Average', Cast(Min(Col_1) as Smallint), Cast(Min(Col_2) as
>Smallint) from MyTable etc works OK.
>Think I may have misunderstood what you were trying to say and only
>cast the first string - i.e Cast('Average' as Varchar(9)) instead of
>the whole statement.
>Changing the first select to Avg() (from Min()) resulted in the same
>error even with one union (it just happened that AVG() was the last of
>the two UNIONS).
>Casting the AVG() to smallint (as per the column datatype) solved the
>problem but I don't understand why when I tried Count() and SUM() also
>gave the same error as AVG() - one would have assumed that they would
>give the same return type as Min() and Max()?

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