Subject Re: [firebird-support] How many Unions allowed in firebird Select?
Author Martijn Tonies
Hello Tony,

> I'm trying to calculate the minimimum, maximum and average values of
> a number of columns in a table thus (as a trial at first):
> 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
> Have tried EMS SQL Editor, IBExpert SQL Editor and TIBOQuery and all
> give the error: SQL Error = -104, Invalid Command, DataType Unkown.
> However, the select works up until the second Union is added. Is
> this a limitation of Firebird?

Naah, you just have to tell it what datatype to use for - in this case -
the first column:

select cast('Minimum' as varchar(9) ), ...
select cast('Maximum' as varchar(9) ), ...

> The idea is to return a table with 3 rows, Minimum, Maximum and
> Average, each row having the relevant column values for columns 1, 2
> etc, - with values ultimately output using a DBGrid.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Upscene Productions
Database development questions? Check the forum!