At 01:13 AM 17/02/2010, you wrote:

>Spasiva!
>
>It results in the same exception, though.
>
>
>The example I gave is of course a simplified draft... would it matter
>that the original statement is joining 2 tables?

What matters here is that you are trying to aggregate on a field that is not in the output list (T.Units). The expression

CASE
WHEN (T.Units > 0) THEN sum(X.Diff/T.Units)
ELSE sum(X.Diff)

is a run-time field calculated from an expression. You *can* group by expression fields, by referring to them in the output by their position number.

However, in this case it is not valid to do so because the expression field is also where the aggregation result is. If you need to apply a filter to an aggregation result, you use the HAVING clause and repeat the expression (see example below).

It is also good practice with expression output to give it an explicit identifier. Not all versions of Firebird behave the same with regard to applying a system-generated name...and in this particular expression, there are potentially two SUM() operations involved...which does not rule out an "invalid expression" error occurring in some edge condition.

Use
select
.....,
(CASE
WHEN (T.Units > 0) THEN sum(X.Diff/T.Units)
ELSE sum(X.Diff) ) AS TOTAL
.....

and note also, although it seems you don't want to filter out zero results here:

HAVING
(CASE
WHEN (T.Units > 0) THEN sum(X.Diff/T.Units)
ELSE sum(X.Diff) ) > 0

because you cannot refer to the run-time identifier here.

./heLen