473,835 Members | 1,633 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

numeric rounding


hey guys..need your help on this..

i have a plpgsql function where in i compute numeric values for my php scripts..

my problem is my function just won't round some numbers properly..

what i want it to do is like this.

example:

721.875 = 721.88
721.865 = 721.87
721.765 = 721.77
721.775 = 721.78
here's my function which returns numeric(12,2):

CREATE OR REPLACE FUNCTION fn_adjust_accum _dep(int4,int4, numeric(12,2)) RETURNS numeric(12,2) AS '

DECLARE

fxamid ALIAS FOR $1;
-- life is in months
life ALIAS FOR $2;
acqamt ALIAS FOR $3;
depmonth int4;
depyear int4;
depdate date;
lastdepdate date;
dyear int4;
dmon int4;
manth int4;
manthlife int4;
depexpense numeric(12,2);
salvagevalue float4;
BEGIN

SELECT
EXTRACT(month FROM fxam_acquisitio n_date),
EXTRACT(year FROM fxam_acquisitio n_date),
fxam_dep_date,
fxam_salvage_va lue/100
INTO
depmonth,
depyear,
lastdepdate,
salvagevalue
FROM fixed_asset_mas ter WHERE fxam_id = fxamid;

-- for Month of December
IF (depmonth = 12) THEN
--Next year
depyear := depyear + 1;
--January the following year
depmonth := 1;

ELSE
depmonth := depmonth + 1;

END IF;

-- first depreciation date of property based on acquisition date
depdate := depmonth || ''/1/'' || depyear;

-- RAISE NOTICE ''depdate = %'', depdate;
-- get number of month and years from first depreciation date to last depreciation date
SELECT
EXTRACT(month FROM AGE(lastdepdate ,depdate::DATE) ),
EXTRACT(year FROM AGE(lastdepdate ,depdate::DATE) )
INTO dmon,dyear;

-- RAISE NOTICE ''dmon=%,dyear= %,depdate=%'',d mon,dyear,depda te;

-- Number of months to depreciate
manth := (dyear * 12) + dmon;
-- Number of months of estimated life
manthlife := life;

-- Number of months to depreciate is greater than number of months of estimated life
-- Only happens when property is encoded late and life is already consumed during first depreciation..
IF ( dyear >= 0 AND manth > manthlife ) THEN

-- Monthly depreciation expense Multiplied by number of month since Acquisition date

depexpense := (acqamt - (acqamt * salvagevalue)) + 0.00000001;

RAISE NOTICE ''manth = % > manthlife = %, depexpense=%'', manth, manthlife,depex pense;

ELSE

-- Monthly depreciation expense Multiplied by number of month since Acquisition date

-- depexpense := ((acqamt - (acqamt * salvagevalue)) / life) * manth;
depexpense := (((acqamt - (acqamt * salvagevalue)) / life) * manth) + 0.00000001;

-- sample data :
-- depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001
-- returns 721.87
-- should return 721.88
-- RAISE NOTICE ''manth = % < manthlife = %, depexpense=%'', manth, manthlife,depex pense;

END IF;

RETURN depexpense;

END;
'LANGUAGE 'plpgsql';

This function is up and running but my boss is such a great debugger.. ;)

You can see I already added a value of 0.00000001 to the computation but I also get the same results.. :(

I know that numeric data type automatically rounds off values but how come it's not returning the right values???

Marie Gezeala M. Bacuño II
IS Department
Muramoto Audio-Visual Phils., Inc.
MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015
The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation.


---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
Nov 12 '05 #1
4 3958
On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' Bacuño II wrote:
my problem is my function just won't round some numbers properly..
what i want it to do is like this.

example:

721.875 = 721.88


a=> select round(721.875, 2);
round
--------
721.88
(1 row)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2
On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' BacuXo II wrote:
You can see I already added a value of 0.00000001 to the computation but I also get the same results.. :(


Why 0.00000001? If you want to round to the second digit by cutting the
number you have to add 0.005.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: me****@jabber.o rg
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3
"Gezeala 'Eyah' \"Bacuño\" II" <ge*******@yaho o.com> writes:
-- sample data :
-- depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001
-- returns 721.87
-- should return 721.88


I get

regression=# select (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001 ;
?column?
----------------------
721.87500001000 00000
(1 row)

I don't see any problem there. If you coerce the value to numeric(12,2)
you get the desired answer:

regression=# select ((((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001)::nu meric(12,2);
numeric
---------
721.88
(1 row)

and the same even without the bogus add-on:

regression=# select ((((2750.00 - (2750.00 * 0.1)) / 24 ) * 7))::numeric(12 ,2);

numeric
---------
721.88
(1 row)

regression=#

I think what's probably happening is your function is returning
"721.8750000100 000000" and something on the client side is simply
dropping digits beyond the ".87".

It may help to point out that although the system will syntactically
accept length limits on function arguments and results, those limits are
not enforced. That is, you wrote

CREATE OR REPLACE FUNCTION fn_adjust_accum _dep(int4,int4, numeric(12,2))
RETURNS numeric(12,2) AS ...

but this is really the same as

CREATE OR REPLACE FUNCTION fn_adjust_accum _dep(int4,int4, numeric)
RETURNS numeric AS ...

If you want the result rounded to 2 digits then you need to apply an
explicit coercion within the function. For example change
RETURN depexpense;
to
RETURN depexpense :: numeric(12,2);
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #4
On Tue, Sep 30, 2003 at 06:13:39PM -0700, Gezeala 'Eyah' Bacuño II wrote:
sorry man, but round function can't be used/doesn't work inside a
plpgsql function..


Huh?

regression=> create or replace function try_to_round(fl oat) returns float as '
regression'> begin
regression'> return round($1::numer ic, 2);
regression'> end;
regression'> ' language plpgsql;
CREATE FUNCTION
regression=> select try_to_round(12 .1231231);
try_to_round
--------------
12.12
(1 row)
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No hay cielo posible sin hundir nuestras raíces
en la profundidad de la tierra" (Malucha Pinto)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1531
by: Ron Adam | last post by:
With all the discussion about numbers here and particularly in regards to prePEP concerning decimal data types got me wondering about how numbers are handled when it comes to rounding. And if that can be improved. My thought/question is, is it possible to have a numeric varable where weather or not it's value has been rounded, and which way, is persistent? For example using 2 bits to represent this you would have something
1
1670
by: Rajesh Kumar Mallah | last post by:
Hi, How come numeric(10,2) accepting value 10.011 and truncating the .001 part ? Shudnt' it be complaining , i am not sure. Regds Mallah.
4
2493
by: Daniel Billingsley | last post by:
I'm trying to figure out what happens when I cast a double with (int)... does it round or drop the decimal part? MS says: "When you convert from a double or float value to an integral type, the value is rounded towards zero to the nearest integral value." Uh... what? I've hard of "rounding to the nearest integral value", "rounding up", "rounding down", but what in tarnation is "rounded towards
3
28249
by: Ken Durden | last post by:
Is it possible to force positive values to have the + sign prefixed on them? double f1 = 1024.2; double f2 = -1024.2; string.Format( "{0:F}", f1 ); // +1024.2 string.Format( "{0:F}", f2 ); // -1024.2 I was hoping for something either in the string.Format prefix, or
9
4168
by: Agnes | last post by:
Where I can find the tutorial about numeric function.. e.g x = 15/6, i need to get the result (2) but not (2.5) also get the reminder y = 15/6 , i can get the result (3) Thanks
11
2262
by: Pieter | last post by:
Hi, I'm having some troubles with my numeric-types in my VB.NET 2005 application, together with a SQL Server 2000. - I first used Single in my application, and Decimal in my database. But a Single with value 4.475 was converted to a Decimal with value 4.4749999999999996D. So after inserting and selecting it from the database I got another value than the original!
4
2271
by: Doug Gray | last post by:
Folks, I am looking for a fast but most importantly a bullet proof method to pass and NMEA data stream (GPS output) ascii numeric strings. The best I can offer is: def fint(a): try: return int(float(a)) except: return 0 The reason for this is the quality of the data from the huge variety of
206
13369
by: md | last post by:
Hi Does any body know, how to round a double value with a specific number of digits after the decimal points? A function like this: RoundMyDouble (double &value, short numberOfPrecisions) It then updates the value with numberOfPrecisions after the decimal
20
5027
by: jacob navia | last post by:
Hi "How can I round a number to x decimal places" ? This question keeps appearing. I would propose the following solution #include <float.h> #include <math.h>
0
9652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10558
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10226
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9343
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7765
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5631
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5802
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3990
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3086
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.