At 09:15 PM 24/03/2007, you wrote:

>Hi All,
>
>My query lokes like this:
>
>SELECT
> V_TXT1,
> sum(I_KOL) AS KOMADA,
> Sum(I_CENA*I_KOL*(1-(F_KASP/100.00))*((100-I_RAB)/100)) AS VP
>FROM GLAVNI_UPIT
>WHERE (I_DAT>=:POC AND I_DAT<=:KRAJ)
>GROUP BY V_TXT1
>ORDER by 2 DESC
>
>Fields I_CENA, I_KOL, F_KASP, I_RAB are by definition of types
>NUMERIC(15,2). If I take to wide range for dates parameters in WHERE
>clausule, I receive error. "arithmetic exception, numeric overflow,
>or string truncation". Maximal value which I get for one of results
>for column VP is "9209924.62677999958" - for dates between 1.1.2007-
>9.3.2007. If I change second date to 10.3.2007, I receive error
>mentioned above.

"The last straw that broke the camel's back". Your total
9209924.62677999958 is within the range of 18 digits of precision
(the limit for a 64-bit integer); when you add one more number to
it, it is overflowing to 19 digits.

>I would apreciate help from someone how to resolve
>this. I use FB 2.0, I tried all decimal datatypes (NUMERIC, DECIMAL,
>FLOAT, DOUBLEPRECISION) as datatype for result - column VP - no
>solution.

You have:

I_CENA*I_KOL ----> 4 places of decimal (call this A)
1-(F_KASP/100.00) ---> 4 places of decimal (call this B)
((100-I_RAB)/100) --> 3 places of decimal (call this C)

then you are doing

A * B * C --> 11 places of decimal, which leaves 7 digits on the left
side of the decimal point. You are summing these and, once the total
flips up to 10 million, you have run out of precision. so you are
getting the overflow.

You could save 2 places in the middle part of the calculation by
making (F_KASP/100.00) into (F_KASP/100). Depending on your numbers,
it might be enough to keep your totals in range, or you might need to
review the whole calculation and see whether you can free up some
more precision by casting some of the intermediate numbers as double
precision, before finally casting the result as numeric(18.2).

Forget floats, they have at best 7 places accuracy.

./heLen