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