Using Case in Order By with Union

Question

I’m having trouble using Order by com Case on Union, so it works:

select tb_a.nr_rua, tb_a.cod_altura, tb_a.cod_posicao, tb_a.cod_lado where ......
union all
select tb_b.nr_rua, tb_b.cod_altura, tb_b.cod_posicao, tb_b.cod_lado where ......
order by
2,3,1

Now if I do so, using the case:

order by 2,
 (case when 4 = 'M' then 3 END) DESC,
 (case when 4 <> 'M' then 3 END),
1

Or:

order by 2,
 (case when cod_lado = 'M' then cod_posicao END) DESC,
 (case when cod_lado <> 'M' then cod_posicao END),
1

Or:

order by 2,
 (case when cod_lado = 'M' then 3 END) DESC,
 (case when cod_lado <> 'M' then 3 END),
1

Also not working. following Error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid command.
invalid ORDER BY clause.

I’m using the Firebird.

Answer 1

Your order by should provide an expression that can be evaluated for each record. When you use:

(case when cod_lado = 'M' then cod_posicao END) DESC,

Expression result missing for records cod_lado <> 'M'.

Maybe you can use something like this:

if(cod_lado = 'M', 0, 1)