RE: Round Off function in SQL

How about adding .5 to your result and then convert to integer.  
Like INTEGER = DECIMAL# + .5 
Does the integer function truncate or round?
I am not sure about the divide by .5 in the example below.


Joe Giusto II
Patuxent Publishing Company
 <mailto:jgiusto@patuxent.com> mailto:JGiusto@patuxent.com
http://www.lifegoeson.com <http://lifegoeson.com> 


        -----Original Message-----
        From:   David Morris [SMTP:dmorris@plumcreek.com]
        Sent:   Monday, January 04, 1999 11:03 AM
        To:     MIDRANGE-L@midrange.com
        Subject:        Re: Round Off function in SQL

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