rounding a field 1

[joeythelips]

Hi,

I want to update a field in an oracle table.
My formula is
-divide field value by .787564.
-round resulting amount to 2 decimal places.
-round down where the third figure after the decimal point is 4 or lower, round up where it is 5 or higher.

I can do the first 2 parts no probs, but do i need to add extra sql to the final part to round up or round down? Or does Oracle automatically work that out? This is the code i' have so far:

UPDATE MyTable set
MyTable.TotalCash1 = round(MyTable.TotalCash1 /1.27, 2)
MyTable.CurrencyType ='EURO'
WHERE MyTable.CurrencyType = 'IRP';

 

[karluk]

Based on a quick test, Oracle does approximately what you want.

SQL> select round(1.245,2) from dual;

ROUND(1.245,2)
--------------
1.25


It seems that the biggest issue is likely to be if you have to handle negative numbers. See what happens:

SQL> select round(1.245,2) from dual;

ROUND(-1.245,2)
---------------
-1.25

The negative number gets rounded to the same "5" in the last digit. That's sort of what you are asking to happen, but the net result is that the number becomes smaller - even more negative than it was before the round.