Subject Re: How many Unions allowed in firebird Select? Tony Masefield 2005-11-06T05:52:58Z
wrote:
>
> Tony,
>
> Consider the following simplified example, assuming Col_1 and Col_2
> are some sort of integer.
>
> MyTable:
>
> Col_1 Col_2
> ----- ------
> 1 1
> 2 2
>
> Now your query looks like this:
>
> Select 'Minimum', Min(Col_1), Min(Col_2) from MyTable
> Union
> Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable
> Union
> Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable
>
> Firebird uses the first query within the union to determine the
> required datatypes, so it decides that the first column is to be a
> char or varchar, the second column is to be integer and the third
> column is to be integer, so the first record to be returned is:
>
> 'Minimum', 1, 1
>
> All is well so far, so the first union is done, and the result
looks
> like this so far:
>
> 'Minimum', 1, 1
> 'Maximum', 2, 2
>
> Now the third union is attempted but there is a problem. Avg Col_1
is
> 1.5, and it isn't going to fit inside an integer field. I mean
> Firebird could force it to fit by rounding it, but then your
results
> would not be as expected. So you end up with an error message.
>
> Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable
> Union
> Select 'Minimum', Min(Col_1), Min(Col_2) from MyTable
> Union
> Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable
>
> Does this work? I haven't tested it at all, but I think this one
will
>
For the records, no it doesn't work.

> force the field to become a float which can hold both the result of
> avg and an integer without problem.
>
> BUT
>
> It is one thing to explain the phenomenon, it is another to come up
> with a solution that works every time.
>
> Firstly, you need to name the fields or the field names are going
to
> be things like Min_1 etc, with no guarantee of not being renamed
in a
> future version of Firebird, and with no sensible meaning for
Maximum
> or Average.
>
> Secondly, you are lucky in this case that the description field is
the
> same length on all lines so that will not cause the issue. Although
> the following query is a bit longer, it should work better.
>
> Select Cast('Minimum' as varchar(7)) as Description, Cast(Min
(Col_1)
> as float) as Col_1, Cast(Min(Col_2) as float) as Col_2 from MyTable
> Union
> Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable
> Union
> Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable
>
> Also, given that duplicates are not possible in this query, union
is
> equivalent to union all. Union all does not have to check for
> duplicates, which may or may not make a difference to performance.
>
> (It is like running select distinct pk_field from mytable, there
is no
> advantage in using distinct here).
>
> Hope that helps