How to use an SQL CASE expression

Read an example of an SQL case expression from our SQL expert Rudy Limeback.

I'm hoping you can help me with a MySQL rounding issue where I have hit the wall. I'm attempting to take an integer column, 'x', from table 'y' and achieve the following:

If x = 0, then x = 0
If x <= 30, then x = 30
If x > 30, then round x to nearest multiple of 6.

I can do the following to round to nearest multiple of 6, but this does not help me for values <= 30:

select ceiling(x/6)*6 from y;

Can you help?

The solution here is to use a CASE expression. I think you might be pleasantly surprised how closely it resembles your original specs:

SELECT CASE WHEN x = 0   THEN 0
            WHEN x <= 30 THEN 30
            WHEN x > 30  THEN CEILING(x/6)*6 
        END AS result
  FROM y

Two comments. First, CASE expressions are evaluated linearly. That is, each condition is evaluated in turn, starting at the top, and the first condition that evaluates to TRUE will determine the resulting value, as given by its THEN expression.

Secondly, your specs did not mention what you wanted if x should happen to be negative. Thus, in the above CASE expression, negative x values would all be assigned the resulting value of 30. Can you see why?

Dig Deeper on Oracle and SQL

SearchDataManagement
SearchBusinessAnalytics
SearchSAP
SearchSQLServer
TheServerSide.com
SearchDataCenter
SearchContentManagement
SearchHRSoftware
Close