Home » SQL & PL/SQL » SQL & PL/SQL » a difference of 0.1
a difference of 0.1 [message #230646] Thu, 12 April 2007 08:08 Go to next message
Messages: 356
Registered: March 2005
Senior Member
Hi All

I have created the following procedure. which procduces a slight error (.1).
e.g. when it divides a value of 500 by 1. it shows 500.1

I wonder how is it possible? The procedure and result is given below.

create or replace PROCEDURE assign_gas(mp IN char,yp IN number ) as
total_attendances number(5);
total_gas_amount number(10,2);
gas_amt_per_att  number(10,2);
d number(3);
  	select count(*) into total_attendances from attendance 
	where flag=1 
	and to_char(a_date,'MON')=mp 
	and to_char(a_date,'YYYY')=yp;
---------------------------------get gas amount for the month----------------
			select amount into total_gas_amount  from gas_rt where g_m=mp and g_y=yp;
			when no_data_found then
			dbms_output.put_line('No data in gas_rt for '||mp||' '||yp);
--------------------------delete from bill any existing gas records-----------------
	select distinct item_id into d from gas_rt;
	delete from bill where  m=mp and y=yp and item_id=d;
------------------------calcualte per member gas charges-------------
	gas_amt_per_att  :=total_gas_amount/total_attendances;
-------------------------calcualte and insert each member gas bill in bill table----------
			cursor c3 is select member_id,sum(flag) as tot_att from attendance
			where to_char(a_date,'MON')=mp
			and to_char(a_date,'YYYY')=yp
			group by member_id;
			for x3 in c3 loop
			dbms_output.put_line(x3.member_id||' '||round(x3.tot_att*gas_amt_per_att,2));
			insert into bill values(x3.member_id,d,round(x3.tot_att*gas_amt_per_att,2),mp,yp);
			end loop;

The Result

Check for GAS line..

  1  select b.member_id,i.item,b.amount,b.m,b.y
  2  from bill b, items i
  3  where b.item_id=i.item_id
  4  and b.member_id=2005001
  5* order by 1
EX > /

MEMBER_ ITEM                                                   AMOUNT M            Y
------- -------------------------------------------------- ---------- --- ----------
2005001 NEWSPAPER                                                  56 APR       2007
2005001 CABLE CHARGES                                              20 APR       2007
2005001 TOPI PROJECT                                               40 APR       2007
2005001 INTERNET CHARGES                                           50 APR       2007
2005001 MESSING                                                  8238 APR       2007
2005001 ICE CREAM                                                 650 APR       2007
2005001 BREAKFAST                                                 112 APR       2007
2005001 GAS                                                     500.1 APR       20072005001 ELECTRICITY                                              5.48 APR       2007

Please guide..
Thank you

Re: a difference of 0.1 [message #230648 is a reply to message #230646] Thu, 12 April 2007 08:22 Go to previous message
Michel Cadot
Messages: 65146
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't analyze in depth your code but you work with a precision of 2 digits, it is not surprising you have a difference on the first digit.

Previous Topic: Getting error ORA-02012: missing USING keyword
Next Topic: SQL Help Needed: Split & Replace - Update Column Value
Goto Forum:

Current Time: Mon Aug 21 03:32:25 CDT 2017

Total time taken to generate the page: 0.01114 seconds