Marco,
I do believe that Adam is pointing you in the right direction.

If you believe its from "... 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" then one
approach to tackle it is to test for the condition first,
which you can do using a nested case statement, as follows.

Your code snippet (adding an else clause) simplifies to:
case when x>0 then (y-x)/x else 0 end * 100

Try:
case when X>0 then case when ((y-x)/100)>x then 1.0 else
(y-x)/x end else 0 end * 100

Also, if you are using Firebird 2.0 then the new derived
tables feature (see the FB2.0 Release Notes) will mean you
need only do each summation once.
HTH
John

> -------Original Message-------
> From: Adam <s3057043@...>
> Subject: [firebird-support] Re: Problem with a division and multiplication in a query
> Sent: 28 Mar '07 09:54
>
> --- In [LINK: mailto:firebird-support%40yahoogroups.com]
> 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
>
>