numeric problem in postgres function...
here's my function :
-- Function : fn_adjust_accum_dep(int4,int4,numeric)
-- Used in computing accumulated depreciation expense during Adjustment Transaction
CREATE OR REPLACE FUNCTION fn_adjust_accum_dep(int4,int4,numeric) RETURNS numeric AS '
DECLARE
fxamid ALIAS FOR $1;
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;
salvagevalue float4;
BEGIN
SELECT
EXTRACT(month FROM fxam_acquisition_date),
EXTRACT(year FROM fxam_acquisition_date),
fxam_dep_date,
fxam_salvage_value / 100
INTO
depmonth,
depyear,
lastdepdate,
salvagevalue
FROM fixed_asset_master
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=%'',dmon,dyear,depdate;
-- 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);
ELSE
-- Monthly depreciation expense Multiplied by number of month since Acquisition date
-- depexpense := ((acqamt - (acqamt * salvagevalue))/ life) * manth;
RAISE NOTICE ''depexpense = %, salvagevalue = %, acqamt = %, life = %, manth = %'',depexpense, salvagevalue, acqamt, life, manth;
-- actual value of numeric
depexpense := (acqamt - (acqamt * salvagevalue)) / life;
RAISE NOTICE ''depexpense = %'',depexpense;
-- cast to numeric(12,2)
depexpense := ((acqamt - (acqamt * salvagevalue))/ life)::numeric(12,2);
RAISE NOTICE ''depexpense = %'',depexpense;
depexpense := depexpense * manth;
RAISE NOTICE ''depexpense = %'',depexpense;
END IF;
RETURN depexpense;
END;
'LANGUAGE 'plpgsql';
============================
sample 1
output using the function (called thru php script):
SELECT fn_adjust_accum_dep(12,24,2750);
result :
NOTICE: depexpense = <NULL>, salvagevalue = 0.1, acqamt = 2750, life = 24, manth = 7
NOTICE: depexpense = 103.124999829258
NOTICE: depexpense = 103.12
NOTICE: depexpense = 721.84
expected output using psql :
fxatst=# select ((2750 - (2750 * 0.1)) / 24);
?column?
---------------------
103.125000000000000
(1 row)
==========================
sample 2
output using the function (called thru php script):
SELECT fn_adjust_accum_dep(22,24,2430);
result :
NOTICE: depexpense = <NULL>, salvagevalue = 0.1, acqamt = 2430, life = 24, manth = 2
NOTICE: depexpense = 91.1249998491257
NOTICE: depexpense = 91.12
NOTICE: depexpense = 182.24
expected output using psql :
fxatst=# select ((2430 - (2430 * 0.1)) / 24);
?column?
--------------------
91.125000000000000
(1 row)
==============================
see the difference?? how come, how come??
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
