If you are running FB2 the new derived tables feature allows you to get a record count for a union if you need one, eg
SQL> select count(*) from
CON> (select 'Derived' as col1 from rdb$database union
CON> select 'Tables ' as col1 from rdb$database);
returns
COUNT
============
2
Cheers,
John

> -------Original Message-------
> From: Helen Borrie <helebor@...>
> Subject: Re: [IBO] RecordCount gives strange error
> Sent: 22 Jan '07 15:56
>
> At 04:21 PM 22/01/2007, you wrote:
> >I have a TIBOQuery that I use as a general-purpose object for allowing
> >users to run ad-hoc queries against the database. This works fine
> >most of the time. However, this one particular query is giving us
> >trouble:
> >
> >select
> >proddesc
> >, prodinventory
> >, proddept
> >, prodsource
> >, prodprice
> >, (prodprice * prodinventory) as Total_Retail_Value
> >, ((select sum(fifocost) from fifocosts B where
> >B.productskey=A.productskey) * prodinventory) as Total_Paid_Value
> >from products A where
> >proddept = 702
> >and prodsource = 'VWS'
> >
> >union all
> >
> >select
> >cast("TOTALS" as varchar(255))
> >,sum(prodinventory)
> >,cast(0 as integer)
> >,cast("" as varchar(80))
> >,cast(0 as double precision)
> >,(sum(prodprice) * sum(prodinventory)) as Total_Retail_Value
> >, (select sum(fifocost) * sum(prodinventory) from fifocosts A join
> >products B on b.productskey=a.productskey where proddept = 702 and
> >prodsource = 'VWS') as Total_Paid_Value
> >from products where proddept = 702 and prodsource = 'VWS'
> >
> >The strange thing is that the query does not immediately fail when
> >executed. It the code, there is a statement that reads the
> >RecordCount property, and when this is done, I get the following error:
> >
> >ISC ERROR CODE:335544569
> >
> >ISC ERROR MESSAGE:
> >Dynamic SQL Error
> >SQL error code = -104
> >Invalid command
> >count of column list and variable list do not match
> >
> >STATEMENT:
> >TIB_Cursor: "<TApplication>.frmSQL.crEdit."
> >
> >If I run the same query in IB_SQL on the Cursor tab, the statement
> >executes fine, and gives me the results, but if I click the "Count
> >Rows in Dataset" icon, I get the same error as above.
> >
> >What is going on here?
>
> You're asking the Fb/IB engine to do something it can't do - in this
> case, to perform select count(*) on a unioned set.
>
> Recordcount belongs to Paradox. There is rarely, if ever, a good
> reason to use it with Fb/IB. For those who insist anyway,
> TIBODataset's inherited RecordCount property resolves to a private
> property RecordCountAll, whose read function constructs a select
> count(*) query based on the set that was specified in the SQL
> property. And of course, select count(*) isn't valid for a union.
>
> Helen
>