HELP with case statement [Divide by zero error encountered.] !

I am getting the same result pandey, I might have left that of by accident.
I
have tried it many ways.
case when sum (dids.supplier_cost) = '0'
then '0'
when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
THEN '0'
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end [dollar_turns],
"Abhishek Pandey" wrote:
> in your case statement you r not checking for dids.supplier_cost = 0
> and in your else clause you are divinding by
> ( (dihs.qty_total) * dids.supplier_cost )
> hope thishelps
> rgds
> abhishek
> "ITDUDE27" wrote:
>i am not sure what exactly are you trying to do here..
in your case statement you are checking "sum(dids.supplier_cost)" and other
things for 0 and handling it accordingly but in else clause you are divindin
g
by dids.supplier_cost.
say for example
dids.supplier_cost has three values
1
0
3
while sum(dids.supplier_cost) <> 0 but this doest garuntee that individual
values are also not zero.
so in this case when it goes to else clause sql server experience a divide
by zero error coz what it will try to do is this
sum( somethingvalue / somenonzerovalue + somevalue/ 0 + somevalue/somevalue)
i hope this is clear enough.
You gotta design your query in a better way. if you are having a hard time
try to post your FULL query and some1 would be able to help you out.
regards
Abhishek
"ITDUDE27" wrote:
> I am getting the same result pandey, I might have left that of by accident
. I
> have tried it many ways.
> case when sum (dids.supplier_cost) = '0'
> then '0'
> when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> THEN '0'
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end [dollar_turns],
> "Abhishek Pandey" wrote:
>