Using Case in Order By with union

Question

Using Case in Order By with union

Navigation

#1 by (0 votes)

0

I'm having trouble using Order by with Case in 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 this, 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

It also does not work. next Error:

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

I'm using Firebird.

sql firebird

asked by anonymous 10.05.2017 / 21:20

1 answer

Check if a screenshot of the screen was taken Active menu after refresh with localStorage

score 0 · Answer 1

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

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

The result of the expression is missing for the records cod_lado < > 'M'.

Maybe you could use something like this:

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