Consider an example statement:
select field1, field2, field3, rt = case cond_field1 when 'B' then 'BUY' when 'S' then 'SELL' end, field4 from some_table |
This uses a conditional case keyword within the SQL statement, which has the potential to be evil.
It is possible that the statement be expanded and rewritten as such:
select field1, field2, field3, 'BUY', field4 from some_table where cond_field1='B'UNIONselect field1, field2, field3, 'SELL', field4 from some_table where cond_field1='S' |
This removes the conditional case keyword and also has the additional benefit a performance improvement should cond_field1 also be part of an index! However, I admit that the second approach does become a bit more unwiedy when there are multiple case conditions within the original statement. The second approach can also be more difficult to maintain since changes to the query have to be done in multiple places (e.g. if a new field5 is to be retrieved as well, the statement has to be updated in 2 places, otherwise the UNION will fail)
In short, the use of CASE can be justified on a case-by-case basis. 