| Subject | RE: [firebird-support] Error in order by clause |
|---|---|
| Author | |
| Post date | 2015-08-25T09:47:27Z |
(Not sure if this is a duplicate thread or the same as "Wrong sort results" but I'll answer here too)
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 sums are NUMERIC.
Even though the result is always the same (dependent on you input I), Firebird doesn't know that yet. For Firebird the result CAN VARY between records and that's not allowed (record one could be a VARCHAR and record 2 could be NUMERIC for all Firebird knows). 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 sums to order right aligned)
---In firebird-support@yahoogroups.com, <bogdan@...> wrote :
Even though the result is always the same (dependent on you input I), Firebird doesn't know that yet. For Firebird the result CAN VARY between records and that's not allowed (record one could be a VARCHAR and record 2 could be NUMERIC for all Firebird knows). 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 sums to order right aligned)
---In firebird-support@yahoogroups.com, <bogdan@...> wrote :
It should be
SUMA A B C D E
12 14 2 1 11 1
0 3 3 2 12 2
-1,1 1,2 2,3 1 11 3
-1,8 3,4 5,2 2 11 3
Suma descending
Regards
Bogdan
----- Reply message -----
Van: "'Bogdan' bogdan@... [firebird-support]" <firebird-support@yahoogroups.com>
Aan: <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] Error in order by clause
Datum: ma, aug. 24, 2015 10:37
order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING