On 21/08/15 14:17, 'Bogdan' bogdan@... [firebird-support] wrote:

> order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E))
> DESCENDING
>
> If i run it with i = 0 it gives wrong sort order.
>
> SUMA A B C D E
> -1,1 1,2 2,3 1 11 3
> -1,8 3,4 5,2 2 11 3
> 0 3 3 2 12 2
> 12 14 2 1 11 1
>

You are using different results in the ORDER BY clause. That's not
allowed. Your t.c is an INTEGER and your E is a varchar but your sum are
NUMERIC.

Even though the result is always the same (dependent on I), Firebird
doesn't know that. For Firebird the result CAN VARY between records and
that's not allowed. So you need to cast them all to the same type.

For instance this will work:

order by iif(:i = 1, cast(t.c as numeric), IIF(:i = 0, sum(t.a) -
sum(t.b), cast(t.e as numeric))) DESCENDING

But if you want to order by E (=varchar) maybe you should cast to
varchar but in that case you need to make sure the ordering is done
correctly (with adding spaces in front of the sum to order right)