how to update an existing procedure & round off numbers to 2 decimal 1

[Ngai88]

Hello,
I am learning as I go ...
I want to make changes to an existing procedure. I want to round the numbers to 2 deciml places.
Example: TIMEDIFF shows up as 0.366666666666667

I want it to show up as 0.37

how do I go about to change that?

Also, after the change, I need to update this procedure. I use SQL plus to do that. How do I go about making the changes stick?

thanks for the help,
Ngai

=======================================================
PROCEDURE "LOGIN_LOGOUT" (sUserName varchar2, iIncidentId integer, iLoginId integer, sSessionId varchar2, iErrorCode out integer)
as

begin
iErrorCode := 2;

update cms_loginhist
set TIME_OUT=get_current_datetime(), TimeDiff=(TO_NUMBER(get_current_datetime()-Time_in))*24
where (LOGINID=iLoginId) and (USERNAME=sUserName) and (INCIDENT_ID=iIncidentId);
iErrorCode := 1;

if sSessionId != 'thick client' then
delete from cms_thinclients where (LOGINID=iLoginId) and (USERNAME=sUserName);
end if;
iErrorCode := 0;


end LOGIN_LOGOUT;

 

[BJCooperIT]

To update, create a text file that you run in SQL*Plus:

drop procedure LOGIN_LOGOUT;
create PROCEDURE "LOGIN_LOGOUT"
 (sUserName varchar2,
  iIncidentId integer,
  iLoginId integer,
  sSessionId varchar2,
  iErrorCode out integer)
as
begin
iErrorCode := 2;

update cms_loginhist
set    TIME_OUT=get_current_datetime(),
       TimeDiff=[COLOR=red]round(([/color](TO_NUMBER(get_current_datetime()-Time_in))*24[COLOR=red]),2)[/color]
where  (LOGINID=iLoginId) and (USERNAME=sUserName) 
and    (INCIDENT_ID=iIncidentId);

iErrorCode := 1;

if sSessionId != 'thick client' then
   delete from cms_thinclients
   where  (LOGINID=iLoginId) and (USERNAME=sUserName);
end if;

iErrorCode := 0;

end LOGIN_LOGOUT;
/

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[Ngai88]

BJCooperII,
Thanks for your prompt help. Everything works fine except the round to 2 decimal is still not working. It still shows up the same ...

something else I need to do to fix this?

thanks,
Ngai.


======================================================
LOGINID USERNAME FUNCTION_NAME INCIDENT_ID TIME_IN TIME_OUT
--------- ---------------- ------------------------------ ----------- --------- ---------
LOCATION_ID TIMEDIFF
---------------- ----------- ---------
2975 test3 Volunteer Organizations 44 17-DEC-04 17-DEC-04
1 .26666667

2985 test3 Volunteer Organizations 44 17-DEC-04 17-DEC-04
1 .26666667

3000 test3 Volunteer Organizations 44 17-DEC-04 17-DEC-04
1 .28333333

3002 test3 Volunteer Organizations 44 17-DEC-04
1

 

[BJCooperIT]

First check the stored procedure to make sure it reflects the 'round'. How are you getting the list above?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[Ngai88]

hi BJCooperIT,
You are right, I looked at the data again, and ran a few more tests, the new data came out correctly. I guess I was still looking at the old data earlier, and of course it did not change, duh me !!! ..lol

Thank you so much for your help.

Ngai