But it also fails using a CAST:

new.fld3 = CAST((new.fld1 * new.fld2) AS NUMERIC(18,6));

What is the best field definition for currency values, with a precision
of at least 6 digits?

Regards,

Luis


Ivan Prenosil wrote:

>> But I am always multiplying by 1.
>> Since fld2 is always 1 (in my tests):
>>
>> new.fld3 = new.fld1 * 1;
>> this shouldn't return any error;
>>
>> But should NUMERIC(18,6) allow for bigger values?
>>
>
> But your intermediate result (before assigning it to fld3) is NUMERIC(18,12).
>
> Ivan
>
>
>
>> Luis
>>
>> Ivan Prenosil wrote:
>>
>>>> I have this weird calculation problem with NUMERIC(16,6) fields.
>>>>
>>>>
>>>> Error Message:
>>>> ----------------------------------------
>>>> Unsuccessful execution caused by system error that does not preclude
>>>> successful execution of subsequent statements.
>>>> Integer overflow. The result of an integer operation caused the most
>>>> significant bit of the result to carry.
>>>> <<<<<<<<<<<<<<<<<<
>>>>
>>>> This shows up in a trigger with this code:
>>>> CREATE TRIGGER OM_BIU1 FOR O_MOVS
>>>> ACTIVE BEFORE INSERT OR UPDATE POSITION 1
>>>> AS
>>>> /* fld1 is NUMERIC(18,6); */
>>>> /* fld2 is NUMERIC(18,6); */
>>>> /* fld3 is NUMERIC(18,6); */
>>>> begin
>>>> new.fld3 = new.fld1 * new.fld2;
>>>> /* fld2 is always equal to 1 */
>>>> end
>>>>
>>>> But this only happens when fld1 is bigger than 9 200 000.
>>>> If I enter 9 000 000 in fld1, it works.
>>>>
>>>>
>>> It is correct. Result of multiplication of two NUMERIC(18,6) values
>>> is NUMERIC(18,12), which means the biggest resulting number is
>>> 9223372.036854775807
>>>
>>>
>>>
>>>
>>>> Unsuccessful execution caused by system error that does not preclude
>>>> successful execution of subsequent statements.
>>>>
>>>>
>>> This is weird, seems like you have another problem ...
>>>
>>> Ivan
>>>
>>>
>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>
>>> Visit http://firebird.sourceforge.net and click the Resources item
>>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>>
>>> Also search the knowledgebases at http://www.ibphoenix.com
>>>
>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://firebird.sourceforge.net and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Yahoo! Groups Links
>>
>>
>>
>>
>>
>>
>>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>