Re: Rounding in SQL

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Alan,

I have never seen a round function in SQL on the AS/400.  The following may 
should work for a field with one decimal place to none.  Remove the sum() if it 
is 
not a group total.

integer(sum(NumVal) + .5 * ((sum(NumVal) + .01) / abs(sum(NumVal) + .01)))

For two decimal places to none:

integer(sum(NumVal) + .5 * ((sum(NumVal) + .001) / abs(sum(NumVal) + .001)))

I would avoid doing this, but it appears to work.

David Morris


>>> Alan Novick <ANovick@bellatlantic.net> 02/17 12:17 PM >>>
Does anyone know how to round a number to the nearest whole number in
SQL?  In other words, represent 13.9 as 14?  When I use the decimal
function it just truncates (13.9 becomes 13).  I thought I remembered a
function called ROUND but cannot find it in the manual.

Thanks,
-- 
Alan Novick
Pressman Toy Corporation
New Brunswick, NJ


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---