[Info-Ingres] Rounding in Ingres

[Anderson, Troy E.]

I worked with a tech support person and determined several options for rounding.  Under the upcoming version of Ingres (2006 R3) there is a round() function being released, so that will be nice for folks on that version.  In the meantime I learned of 3 options.  I'll show those here in the following examples:

Method #1:

 1> select FLOAT8(INT4((-1.3456 * int4(-1.3456/abs(-1.3456))* (10 ** 3)) +  0.5)) /FLOAT8((10 ** 3)) * int4(-1.3456/abs(-1.3456))
┌───────────┐
│col1       │
├───────────┤
│     -1.346│
└───────────┘
  2> select FLOAT8(INT4((-1.3454 * int4(-1.3454/abs(-1.3454))* (10 ** 3)) +  0.5)) /FLOAT8((10 ** 3)) * int4(-1.3454/abs(-1.3454))
┌───────────┐
│col1       │
├───────────┤
│     -1.345│
└───────────┘
  1> select FLOAT8(INT4((1.3456 * int4(1.3456/abs(1.3456))* (10 ** 3)) + 0.5))/FLOAT8((10 ** 3)) * int4(1.3456/abs(1.3456))
┌───────────┐
│col1       │
├───────────┤
│      1.346│
└───────────┘
  2> select FLOAT8(INT4((1.3454 * int4(1.3454/abs(1.3454))* (10 ** 3)) + 0.5))/FLOAT8((10 ** 3)) * int4(1.3454/abs(1.3454))
┌───────────┐
│col1       │
├───────────┤
│      1.345│
└───────────┘



 Method #2:
 
  1> select decimal((decimal(1.3452*1000*int4(1.3452/abs(1.3452)),9,3)+0.5)/1000,9,3)*int4(1.3452/abs(1.3452))
┌──────────────────────┐
│col1                  │
├──────────────────────┤
│                 1.345│
└──────────────────────┘
  1> select decimal((decimal(1.3456*1000*int4(1.3456/abs(1.3456)),9,3)+0.5)/1000,9,3)*int4(1.3456/abs(1.3456))
┌──────────────────────┐
│col1                  │
├──────────────────────┤
│                 1.346│
└──────────────────────┘
 1> select decimal((decimal(-1.3452*1000*int4(-1.3456/abs(-1.3452)),9,3)+0.5)/1000,9,3)*int4(-1.3452/abs(-1.3452))
┌──────────────────────┐
│col1                  │
├──────────────────────┤
│                -1.345│
└──────────────────────┘

 1> select decimal((decimal(-1.3456*1000*int4(-1.3456/abs(-1.3456)),9,3)+0.5)/1000,9,3)*int4(-1.3456/abs(-1.3456))
┌──────────────────────┐
│col1                  │
├──────────────────────┤
│                -1.346│
└──────────────────────┘



Method #3

  1> select decimal((1.3456*int4(1.3456/abs(1.3456))+(0.5/(10**3)))*int4(1.3456/abs(1.3456)),9,3)
┌───────────┐
│col1       │
├───────────┤
│      1.346│
└───────────┘
  2> select decimal((1.3452*int4(1.3452/abs(1.3452))+(0.5/(10**3)))*int4(1.3452/abs(1.3452)),9,3)
┌───────────┐
│col1       │
├───────────┤
│      1.345│
└───────────┘
  1> select decimal((-1.3456*int4(-1.3456/abs(-1.3456))+(0.5/(10**3)))*int4(-1.3456/abs(-1.3456)),9,3)
┌───────────┐
│col1       │
├───────────┤
│     -1.346│
└───────────┘
  1> select decimal((-1.3452*int4(-1.3452/abs(-1.3452))+(0.5/(10**3)))*int4(-1.3452/abs(-1.3452)),9,3)
┌───────────┐
│col1       │
├───────────┤
│     -1.345│
└───────────┘ 

-----Original Message-----
From: info-ingres-bounces at kettleriverconsulting.com [mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of flip+ at andrew.SeeEmmYou.EeeDeeYou.mmleo.com
Sent: Wednesday, April 11, 2007 10:22 AM
To: info-ingres at kettleriverconsulting.com
Subject: Re: [Info-Ingres] Rounding in Ingres

"Anderson, Troy E." <AndersonTE at bvsg.com> writes:
> select decimal(1.3451,6,2)  ==> results in the value 1.34, where I'd
> like a value of 1.35.   

>I also have to deal with negative numbers, where this math trick needs 
>to work differently, so I've had to write a case statement to use a 
>decimal conversion adding .005 for positive numbers and a subtracting 
>.005 for negative numbers.

how about
select decimal(data1 + (data1/abs(data1) * .005),6,2)

?

--
flip
Just on the border of your waking mind, There lies - Another time,  Where darkness & light - are one. And as you tread the halls of sanity,
  You feel so glad to be, Unable to go beyond.  ELO - Twilight Prologue

_______________________________________________
Info-Ingres mailing list
Info-Ingres at kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20070411/0c2e846f/attachment.html