May 10, 2005 at 1:27 am
Hi,
Im a real newbie to T-SQL and really need some help with what may seem an easy problem to deal with.
I have a huge union query that gets reporting data from a table called rep and another table called cit.
The problem that I have is that if users have entered '0' into the rep table then I get DIV Zero Hell. I have the following as an example:
SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 0)
THEN dbo.rep.repSpend ELSE 0 END) *
MAX(CASE dbo.cit.citMarkUp WHEN ISNULL(dbo.cit.citMarkUp, 0)
THEN dbo.cit.citMarkUp ELSE 0 END) /
SUM(CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0.000001)
THEN dbo.rep.repCPAPrimary ELSE 0.000001 END) AS cur_cpa
I am finding that if the user has not completed any data in the rep.repCPAPrimary field then the default (0.000001) is not used and I get a DIV 0 error.
Should I CAST the field to solve the problem or is there a better way?
Thank you
Damien
May 10, 2005 at 2:22 am
... /
SUM(CASE dbo.rep.repCPAPrimary
WHEN 0 THEN 0.000001
WHEN ISNULL(dbo.rep.repCPAPrimary, 0.000001) THEN dbo.rep.repCPAPrimary
ELSE 0.000001 END
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 10, 2005 at 2:24 am
Sorry - missed a bracket on the end.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 11, 2005 at 1:54 am
As I understand it, you have problems when there are no records to be summed in the rep table. If so, then I would propose to check only after the aggregation, whether the entire sum is not equal to 0. Not having seen the entire query, I can't be sure, but I think that the part you posted could be simplified to this:
SUM(ISNULL(dbo.rep.repSpend, 0)) * MAX(ISNULL(dbo.cit.citMarkUp, 0))
/
CASE WHEN SUM(ISNULL(dbo.rep.repCPAPrimary,0)) = 0 THEN 0.000001
ELSE SUM(dbo.rep.repCPAPrimary) END
If I'm wrong, please tell me what I'm missing, post more of the SQL, and I'll try to do better :-).
May 11, 2005 at 1:58 am
No, sum will give you zero when there are no rows. The problem is that if '0' is in rep.repCPAPrimary, then it gives him a DivZero error.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 11, 2005 at 2:13 am
Hi Everyone,
Thank you so far for your replies to my newbie question. The full SQL for that query is:
SELECT
MAX(dbo.cit.citModID) AS mid,
dbo.cit.citID,
MAX(cit.citCamID) AS cam_id,
MAX(CASE dbo.cit.citBudget WHEN ISNULL(dbo.cit.citBudget, 0) THEN dbo.cit.citBudget ELSE 0 END) AS rev,
SUM(CASE dbo.rep.repCpaPrimary WHEN ISNULL(dbo.rep.repCpaPrimary, 0) THEN dbo.rep.repCpaPrimary ELSE 0 END) *
MAX(CASE dbo.cit.citSR WHEN ISNULL(dbo.cit.citSR, 0) THEN dbo.cit.citSR ELSE 0 END) AS cost,
MAX(CASE dbo.cit.citBudget WHEN ISNULL(dbo.cit.citBudget, 0) THEN dbo.cit.citBudget ELSE 0 END) -
SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 0) THEN dbo.rep.repSpend ELSE 0 END) *
MAX(CASE dbo.cit.citMarkUp WHEN ISNULL(dbo.cit.citMarkUp, 0) THEN dbo.cit.citMarkUp ELSE 0 END) AS profit,
SUM(CASE dbo.rep.repImpressions WHEN ISNULL(dbo.rep.repImpressions, 0) THEN dbo.rep.repImpressions ELSE 0 END) AS imps_td,
SUM(CASE dbo.rep.repClicks WHEN ISNULL(dbo.rep.repClicks, 0) THEN dbo.rep.repClicks ELSE 0 END) AS clicks_td,
SUM(CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0) THEN dbo.rep.repCPAPrimary ELSE 0 END) AS apps_td,
0 AS dtd,
SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 0) THEN dbo.rep.repSpend ELSE 0 END) *
MAX(CASE dbo.cit.citMarkUp WHEN ISNULL(dbo.cit.citMarkUp, 0) THEN dbo.cit.citMarkUp ELSE 0 END) AS c_spent_td,
MAX(CASE dbo.cit.citBudget WHEN ISNULL(dbo.cit.citBudget, 0) THEN dbo.cit.citBudget ELSE 0 END) -
SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 0) THEN dbo.rep.repSpend ELSE 0 END) *
MAX(CASE dbo.cit.citMarkUp WHEN ISNULL(dbo.cit.citMarkUp, 0) THEN dbo.cit.citMarkUp ELSE 0 END) AS remrev,
CAST(SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 0) THEN dbo.rep.repSpend ELSE 0 END /
CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0.000001) THEN dbo.rep.repCPAPrimary ELSE 0.000001 END) AS float(10,2)) AS cur_cpa,
CAST(SUM(CASE dbo.rep.repClicks WHEN ISNULL(dbo.rep.repClicks, 0) THEN dbo.rep.repClicks ELSE 0 END /
CASE dbo.rep.repImpressions WHEN ISNULL(dbo.rep.repImpressions, 0.000001) THEN dbo.rep.repImpressions ELSE 0.000001 END * 100) AS float(10,3)) AS cur_ctr,
CAST(SUM(CASE dbo.rep.repClicks WHEN ISNULL(dbo.rep.repClicks, 0) THEN dbo.rep.repClicks ELSE 0 END) /
SUM(CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0.000001) THEN dbo.rep.repCPAPrimary ELSE 0.000001 END) AS float(10,3)) AS cur_cta,
CAST(SUM(CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0) THEN dbo.rep.repCPAPrimary ELSE 0 END /
CASE dbo.rep.repImpressions WHEN ISNULL(dbo.rep.repImpressions, 0.000001) THEN dbo.rep.repImpressions ELSE 0.000001 END * 100) AS float(10,3)) AS cur_ita,
GETDATE() + MAX(CASE dbo.cit.citBudget WHEN ISNULL(dbo.cit.citBudget, 0) THEN dbo.cit.citBudget ELSE 0 END) /
CAST(SUM(CASE dbo.rep.repSpend WHEN ISNULL(dbo.rep.repSpend, 1) THEN dbo.rep.repSpend ELSE 1 END) as decimal(10,2)) -
MAX(CASE dbo.dayslive.DaysLive WHEN ISNULL(dbo.dayslive.DaysLive, 0) THEN dbo.dayslive.DaysLive ELSE 0 END) AS f_end,
MAX(CASE dbo.dayslive.DaysLive WHEN ISNULL(dbo.dayslive.DaysLive, 0) THEN dbo.dayslive.DaysLive ELSE 0 END) AS dayslive,
SUM(CASE dbo.rep.repCPAPrimary WHEN ISNULL(dbo.rep.repCPAPrimary, 0) THEN dbo.rep.repCPAPrimary ELSE 0 END) AS sum_cpa1,
SUM(CASE dbo.rep.repCPASecondary WHEN ISNULL(dbo.rep.repCPASecondary, 0) THEN dbo.rep.repCPASecondary ELSE 0 END) AS sum_cpa2,
SUM(CASE dbo.rep.repCPATertiary WHEN ISNULL(dbo.rep.repCPATertiary, 0) THEN dbo.rep.repCPATertiary ELSE 0 END) AS sum_cpa3,
MAX(CASE dbo.cit.citQOP WHEN ISNULL(dbo.cit.citQOP, 0) THEN dbo.cit.citQOP ELSE 0 END) AS QOP,
MAX(CASE dbo.cit.citBudget WHEN ISNULL(dbo.cit.citBudget, 0) THEN dbo.cit.citBudget ELSE 0 END) AS Budget,
MAX(CASE dbo.cit.citCR WHEN ISNULL(dbo.cit.citCR, 0) THEN dbo.cit.citCR ELSE 0 END) AS ClientRate,
MAX(CASE dbo.cit.citSR WHEN ISNULL(dbo.cit.citSR, 0) THEN dbo.cit.citSR ELSE 0 END) AS SupplierRate
FROM dbo.dayslive INNER JOIN
dbo.cit ON dbo.dayslive.citID = dbo.cit.citID LEFT OUTER JOIN
dbo.rep ON dbo.cit.citID = dbo.rep.repCitID
WHERE dbo.cit.citModID = 1
GROUP BY dbo.cit.citID
May 11, 2005 at 2:13 am
Well, yes, but he is doing divide by SUM... so the error should occur only if the entire SUM = 0. This could occur either if there are no records at all to be summed, or if in all of them is zero.
Anyway, I see now that you're right, Rob - I didn't describe it correctly in my previous post - but only in words, the SQL should be fine and solve this problem. It is not necessary to add 0.000001 to the sum every time a 0 value is encountered... one just needs to make sure that the final sum <> 0.
May 11, 2005 at 2:17 am
Hi Veteran,
Given that it should be <> 0 then how would I handle the fields where they are = 0? I have only put the 0.0001.... there as a place holder because when the value was 0 I was getting the DIV 0 nightmare.
Thanks,
Damien
May 11, 2005 at 2:32 am
Nice stuff, Damien... well, I suppose that's the question that should have been asked first. I only tried to optimize existing code, without venturing deeper into the idea.
What do YOU want to do with the data if the sum is zero? I don't like the idea of a placeholder like that, because then instead of an error you get a nonsense. 100 / 0.000001 = 100000000 ... that's not really good, is it?
I would preface this query with a test, whether a result can be obtained - i.e., whether all divisions will be with non-zero values - and if not, display info about the problem. Look at the use of IF, BEGIN, END, ELSE in BOL, that could help to give you some idea how to do it.
Regardless of that, I think that all or most of the CASE statements you have there can be avoided the way I posted before (MAX(ISNULL(dbo.cit.citMarkUp, 0)) instead of MAX(CASE dbo.cit.citMarkUp WHEN ISNULL(dbo.cit.citMarkUp, 0)
THEN dbo.cit.citMarkUp ELSE 0 END).
May 11, 2005 at 9:11 am
If you just want to avoid the error, SET ARITHABORT OFF. The value for that column will be NULL if it's divided by 0.
May 11, 2005 at 10:05 am
Hi,
Where do i set this value?
Thanks,
Damien
May 11, 2005 at 10:10 am
at the beginning of the stored proc. and turn it back on at the end.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
May 11, 2005 at 10:48 am
If you don't have to return results when the SUM is zero, you could try using:
HAVING SUM(dbo.rep.repCPAPrimary) > 0
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply