select mynumber
, mynumber/1000.0 as dividedby1000
, ceiling(mynumber/1000.0) as ceilingresult
, ceiling(mynumber/1000.0)*1000 as myfinalnumber
from mynumbers
CEILING returns the smallest integer which is equal to or greater than the number it is given. Notice that we need a complex expression involving CEILING, in order to get the "rounded
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
up to next 1000" number that you wanted. The "trick" (if you want to call it that) is to divide by 1000.0, which forces a decimal result, before applying the CEILING.
The query above also illustrates how to go about testing a complex expression like this. Build it up, one step at a time, in separate columns.
The results of the query on the data you provided are:
mynumber dividedby1000 ceilingresult myfinalnumber 460 0.460000 1 1000 1000 1.000000 1 1000 1001 1.001000 2 2000 1550 1.550000 2 2000 2001 2.001000 3 3000
Neat, eh?
Oracle White Papers: Fusion Middleware