Re: Round Off function in SQL

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Prakash,

I asked the same question in August, no response.  The best I could 
come up with was the following:

SELECT integer(((decimal(sum(NumVal),11,2) - integer(sum(NumVal))) / .5)) +
               integer(sum(NumVal)) 
FROM File

In our case it was acceptable but I would like to find a better solution.

David Morris


>>> "prakash subramanian" <prakashsubramani@hotmail.com> 01/04 6:39 AM >>>

Hi,

We have some interest calculation programs based on different criteria 
running in our sales module. We require in many cases, our net value to 
be rounded off to the nearest value. We have some rough programs that do 
the round off in RPG III. We also take lot of print outs on regular 
basis, from interactive SQL(Using Option13) with complex queries that 
are difficult via Query/400.  When I tried to do a round off(Of course, 
I have alienated the RPG III programs as I found they are really 
cumbersome) from interactive SQL, I found to my amazement there is no 
real Round Off Function Available. Though I found FLOOR function. I also 
found that there was no CEIL function available. Though these functions 
are basic to any SQL, it is still amazing that these are not posible via 
Intearctive SQL in As/400. Ofcourse, there is Roundoff to nearest value 
in Query/400, but as I mentioned Query/400 is simply hard in our case. I 
also checked SQL manuals, there is no mention about Round Off or CIEL. 
Am I missing something? or is it really not possible with Interactive 
SQL or do I have to Dealienate those monsterous RPG programs again(Of 
course They have Half adjust in them). Any help is appreciated.

Thanks in advance 

Prakash

+---
| 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
+---