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

Hi,
I have a case statement that has been giving me hell for the past day, can
anyone suggest another way of validating is those field have zeros or not.
The field types are numberic, looking at the table their is no Null values i
n
there a few hundred zeros (0, .000). I have tried this statement in many wa
y
still the same result.
Divide by zero error encountered.
CASE 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
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end as [dollar_turns],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:

> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE 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
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],
>|||By any chance are you rinning SQL 2005? I seem to recall someone else
having a similar issue where the optimizer evaluated the whole
statement rather than one test at a time.
Stu
ITDUDE27 wrote:
> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE 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
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],|||Hi
Your assumption is wrong :)
Let me explain it.
For simplicity I am showing the columns as a(supplier_cost)
,b(qty_total),c(revenue)
lets say the table is like this...
a b c
1 1 1
0 0 0
For all the cases the sum is going to be 1 (for all three columns)
But analyze this..
sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
its actually 1/(1*1) + 0/(0*0)
I guess maybe you are trying for something like this in your else clause..
sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
If not change your logic accordingly.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This thread illustrates exactly why one of the best ways to get help is to
include the *ACTUAL* table DDL code *AND* some sample data INSERT
statements.
No so many back-and-forths just trying to understand the issue.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6DEF9528-1748-4905-ACEE-0567A384126B@.microsoft.com...
> Hi
> Your assumption is wrong :)
> Let me explain it.
> For simplicity I am showing the columns as a(supplier_cost)
> ,b(qty_total),c(revenue)
> lets say the table is like this...
> a b c
> 1 1 1
> 0 0 0
> For all the cases the sum is going to be 1 (for all three columns)
> But analyze this..
> sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> its actually 1/(1*1) + 0/(0*0)
> I guess maybe you are trying for something like this in your else clause..
> sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
> If not change your logic accordingly.
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>sql