Subject: RE: Round Off function in SQL From: Joe Giusto Date: Tue, 5 Jan 1999 17:04:25 -0500

```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.

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

```