Problem with query after upgrade FB 2.1x to 2.5.1

[Ron]

Tools: Delphi 2006, IB Objects 4.9.14

Query:
SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
FROM LABELS

previously worked. Now I get an error:

Invalid expression in the select list (not contained in either aggregate function or GROUP BY clause

However when I run the query from the SQL editor in Firebird Maestro it works and gives the correct result.

Any ideas on why this won't work from Delphi\IB Objects?

[Dmitry Kuzmenko]

Hello, Ron!

Monday, April 9, 2012, 11:45:38 AM, you wrote:

R> Tools: Delphi 2006, IB Objects 4.9.14

R> Query:
R> SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
R> FROM LABELS

R> previously worked. Now I get an error:

yes, and it perfectly works on Firebird 2.5.0 and
2.5.1 - just checked.

Seems that something in IBObjects or your application
executes another query in addition, resulting that
strange error message.

So, the reason of error is not in the upgrading FB.

[Mark Rotteveel]

Post by Dmitry Kuzmenko
Hello, Ron!
R> Tools: Delphi 2006, IB Objects 4.9.14
R> SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
R> FROM LABELS
yes, and it perfectly works on Firebird 2.5.0 and
2.5.1 - just checked.
Seems that something in IBObjects or your application
executes another query in addition, resulting that
strange error message.
So, the reason of error is not in the upgrading FB.

Ron, you could try to use the trace API to see what query is actually
being sent to the server.

[Ron]

Mark,

Good suggestion. Svein Tysvær noted that changing a setting in IB Objects might solve the problem and it did.

Thanks again for the suggestion. I appreciate it.

Rick

Post by Mark Rotteveel

Post by Dmitry Kuzmenko
Hello, Ron!
R> Tools: Delphi 2006, IB Objects 4.9.14
R> SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
R> FROM LABELS
yes, and it perfectly works on Firebird 2.5.0 and
2.5.1 - just checked.
Seems that something in IBObjects or your application
executes another query in addition, resulting that
strange error message.
So, the reason of error is not in the upgrading FB.

Ron, you could try to use the trace API to see what query is actually
being sent to the server.
--
Mark Rotteveel

[Ron]

Hello Dmitry,

You're right it was something in IB Objects. Svein Tysvær suggested I set the KeyLinksAutoDefine to false. I did and the query worked as written.

Thanks for your help.

Ron

Post by Dmitry Kuzmenko
Hello, Ron!
R> Tools: Delphi 2006, IB Objects 4.9.14
R> SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
R> FROM LABELS
yes, and it perfectly works on Firebird 2.5.0 and
2.5.1 - just checked.
Seems that something in IBObjects or your application
executes another query in addition, resulting that
strange error message.
So, the reason of error is not in the upgrading FB.
--
Dmitry Kuzmenko, www.ib-aid.com

[Svein Erling Tysvær]

Post by Ron
Tools: Delphi 2006, IB Objects 4.9.14
SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
FROM LABELS
Invalid expression in the select list (not contained in either aggregate function or GROUP BY clause
However when I run the query from the SQL editor in Firebird Maestro it works and gives the correct result.
Any ideas on why this won't work from Delphi\IB Objects?

I'm impressed that it worked - I've never thought it was possible to further manipulate an aggregate result within the expression itself!

Given that your query is quite unusual, I guess it is likely to be an IBO issue (e.g. I know IBO often adds RDB$DB_KEY to the select if you have KeyLinksAutoDefine (or similar) set to true - change it to false and your error may disappear), and as such it is more appropriate to ask on the ibobjects list than here. However, I would expect the following (and probably more common) workarounds to work:

a)
WITH TMP(CountLabels) as
(SELECT COUNT(*) FROM LABELS)
SELECT (CountLabels+0.00)/2 as "LabelsCount"
FROM TMP

b)
SELECT SUM(0.5) as "LabelsCount" FROM LABELS

HTH,
Set

[Ron]

Svein,

Thank you very much. I set KeyLinksAutoDefine to false and the query worked as written. Also found the workarounds fascinating and educational.

Thanks again.

Ron

Post by Svein Erling Tysvær

Post by Ron
Tools: Delphi 2006, IB Objects 4.9.14
SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
FROM LABELS
Invalid expression in the select list (not contained in either aggregate function or GROUP BY clause
However when I run the query from the SQL editor in Firebird Maestro it works and gives the correct result.
Any ideas on why this won't work from Delphi\IB Objects?

I'm impressed that it worked - I've never thought it was possible to further manipulate an aggregate result within the expression itself!
a)
WITH TMP(CountLabels) as
(SELECT COUNT(*) FROM LABELS)
SELECT (CountLabels+0.00)/2 as "LabelsCount"
FROM TMP
b)
SELECT SUM(0.5) as "LabelsCount" FROM LABELS
HTH,
Set