Is there a case against the use of CASE?

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'
UNION
select 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. (smile)

Leave a Reply

Your email address will not be published.