Adam,

Your exemple with rdb$database and typed pictures works well. I've
tested it before than post this trouble. It happens only with fields
and not so simple queries. My situation is more complex, I has a
group clause to get the sums, etc.

But anyway, thanks a lot about the CAST it worked. It is not
beaultiful but works. I have use some tricks like this sometime. For
exemple, for (A * B * (1 - C / 100)) I get different results if I use
(A * B * (1.0 - C / 100.0)) or (A * B * (1.00 - C / 100.00)). If I
use (A * B * (1.00000 - C / 100.00000)) I get Arithmetic error.

Thanks,

Marco Castro

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- 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
>