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 origin
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
al 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?
Oracle White Papers: Fusion Middleware