Hi Helmut,

> in the before update trigger of a table I set a field of the type
> numeric (10, 2) depending on the value of another numeric field.
> I need it rounded to 1 or 2 decimals depending on another field.
> Here is a sample of the trigger:
>
> if (new.num_decimals = 0) then
> new.value1 := round (new.value2);
> else
> if (new.num_decimals = 1) then
> new.value1 = round (new.value2 * 10) / 10;
> else
> new.value1 = round (new.value2 * 100) / 100;
>
>
> If I do so, in some cases new.value1 is set to 0, allthough it must
> be a value other than 0.
>
> If I change the trigger to:
>
> if (new.num_decimals = 0) then
> new.value1 := round (new.value2);
> else
> if (new.num_decimals = 1) then
> new.value1 = round (new.value2 * 10) / 10.0;
> else
> new.value1 = round (new.value2 * 100) / 100.0;
>
> it works as expected.
>
> Why is there a difference between dividing through 100 and 100.0?

Yes, there is.

100 means an integer divide, while 100.0 means a floating point
divide. This is one of the Dialect 3 thingies that you need to
watch out for.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com