Sorry, my mistake. Coalesce look for NOT NULL not for =0 ,sorry!

I think this will ok!

select

case sum(SUM_B)

when 0 then sum(SUM_A) as SUMX

else 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



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Mercea Paul
Sent: Saturday, March 03, 2007 7:47 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Help needed, division fails (arithmetic
exception, numeric overflow etc),



Just a hint

Try

select
sum(SUM_A)/COALESCE(sum(SUM_B),1) 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

Regards,

Paul

From: firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com>
[mailto:firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> ] On Behalf Of dpanidis
Sent: Saturday, March 03, 2007 3:39 AM
To: firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com>
Subject: [firebird-support] Help needed, division fails (arithmetic
exception, numeric overflow etc),

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.

Anyone has come across anything like this? any help will be greatly
appreciated, thanks in advance.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]