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

Reply via email to