dpanidis wrote:

> I searched the messages but I didn't get a message related to my case,
> so I figured I should post.
>
> lets simplify the example and say :
>
> table X
> column A double precision
> column B double precision
> column F smallint
>
> if I do:
> select sum(a)/sum(b) from X no problem, everything is OK
>
> If I do:
>
> select
> case when flag=1 then sum(a) else 0 end as SUM_A,
> case when flag=1 then sum(B) else 0 end as SUM_B,
> F
> from X
> group by F
>
> again no problem, everything is OK
>
> now, what I want to do is the following:
> divide SUM_A/SUM_B, the two pseudo columns of the above example
>
> in other words
>
> select
> sum(SUM_A)/sum(SUM_B) as SUMX,
> F
> from
> ( select
> case when flag=1 then sum(a) else 0 end as SUM_A,
> case when flag=1 then sum(B) else 0 end as SUM_B,
> F
> from X
> group by F)
> group by F
>
> I get the 'arithmetic exception, numeric overflow, or string truncation'
> strange thing is that multiplication
>
> select sum(SUM_A) * sum(SUM_B) as SUMX,
>
> works alright.
> Also if I try to divide straight double precision numbers like
> "12345.12345 / 12345.12345 as as SUMX", again no problem.
>
> I tried lots of workarounds such as casting the pseudo columns in
> other datatypes to no avail.
>
> also i tried to fool firebird and instead of sum(SUM_A)/sum(SUM_B) as
> SUMX, i did sum(SUM_A) * (1/sum(SUM_B)) as SUMX, but again I failed.
>
> I'm certain that no division by zero occurs, even if i reverse the
> order i.e. sum(SUM_B)/sum(SUM_A) as SUMX, I still get the error.

Just to check that you're right that there's no
division by 0. Could you try:

select distinct F from X X1
where not exists(
select * from X X2
where X1.F = X2.F
and X2.Flag = 1
and X2.B <> 0)

Set