--- In firebird-support@yahoogroups.com, "Marco Castroo" <mcastro@...>
wrote:

>
>
> Hello,
>
> I have a somewhat complex query that works but in some situations it
> doesn't. Don't take care about the name of fields. The calculated field
> is a divistion of some fields to get a percentage of something. The
> problem happens when I try to multiply everything by 100 and diference
> of the divisor gets a very low value if compared to the dividend.
>
> Part of the query is this:
>
> Case When Sum(es.PrecoMedio * (sn.QuantSaida - sn.QuantDevolucao)) > 0
> then
> (Sum(sn.ValorTotalSaida - sn.ValorTotalDevolucao) - Sum(es.PrecoMedio
> * (sn.QuantSaida - sn.QuantDevolucao))) /
> Sum(es.PrecoMedio * (sn.QuantSaida - sn.QuantDevolucao)) end * 100
> Lucro_Perc,
>
> I check if the division is possible, not to get zero value. The
> problem is in * 100 (red text). If the division is something like 30/12,
> ok. But if I get 43.25/1.85 and multiply this by 100, I get the message:
> Integer overflow. The result of an integer operation caused the most
> significant bit of the result to carry.
>
> I've already tested all the combinations like place "* 100" before
> end, put 10000 / before the calculus. Placing 0.01 * before the calculus
> I get better results.
>
> Is there a way to just the query without taking a long time of tests
> and different situations? Is my query wrong or it is a bug? I vote for a
> bug.
>
> Thanks,
>
> Marco Castro

Marco,

You must be an extremely confident gambler to vote bug without
providing at least a test case.

I can't get any combination of

select 43.25/1.85 * 100
from RDB$DATABASE

to overflow, which seems to be what you are implying.

Until you provide some SQL script to replicate your problem, my vote
is that you need to explicitly cast your field into something large
enough to handle the results (and obviously intermediate results) for
calculations you are performing.

Try casting as Double Precision or Numeric. You may find the
problematic number is much larger than anticipated.

Adam