Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stark8352
Starting Member
6 Posts |
 Posted - 2011-09-22 : 13:29:17
|
| Hi Guys,I have an sql stored procedure which analyses the time of the first goal in a match into different time slots. I wanted the procedure to display all time slots even if one of the selected team games did not have a first goal in that time. Therefore I have used a LEFT OUTER JOIN to achieve this. However, the statement fails for those time slots that have no entries as it is trying to divide by zero so correctly throws an error. I have read around about possibly using ISNULL or NULLIF but can't seem to get it to work. I'm looking to just have those rows return zeros.My code is as follows: SELECT Markets.MarketName, COUNT(Scorers.mktTFLG) AS Home, ROUND(CAST(COUNT(Scorers.mktTFLG) AS FLOAT) / (SELECT CAST(COUNT(Scorers_2.mktTFLG) AS FLOAT) AS Expr1 FROM Markets AS Markets2 LEFT OUTER JOIN Scorers AS Scorers_2 ON Markets2.mktID = Scorers_2.mktTFLG AND Scorers_2.TeamID = @TeamID AND Scorers_2.HOA = 'Home' AND Scorers.LastGoal = 'Yes' WHERE (Markets2.Description = 'FirstLastGoals')) * 100, 1) AS Expr1FROM Markets LEFT OUTER JOIN Scorers ON Markets.mktID = Scorers.mktTFLG AND Scorers.TeamID = @TeamID AND Scorers.HOA = 'Home' AND Scorers.LastGoal = 'Yes'WHERE (Markets.Description = 'FirstLastGoals')GROUP BY Markets.MarketName, Markets.mktID, Scorers.LastGoalORDER BY Markets.mktIDI have other queries similar to this that do produce a 0 where no rows match and that's without an ISNULL or NULLIF, so I'm not sure what's going on there!Anyway I would appreciate it if anyone could advise me how I get the unmatched rows to just return zero and where in the statement any ISNULL or NULLIF should be placed.Thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-22 : 13:33:11
|
| Use a CASE Statement for this.Case WHEN someVal = 0 THEN 0 ELSE a/b END as Expr1 |
 |
|
|
stark8352
Starting Member
6 Posts |
Posted - 2011-09-22 : 14:17:55
|
| russell,You're a star, that works perfectly.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 15:07:33
|
Probably too convoluted but you could doA / NullIf(B, 0) which will give you NULL if B is 0, rather than divide-by-zero-errorand then you could doIsNull(A / NullIf(B, 0), 0) to convert that NULL back to a zero.It may be more efficient than a CASE statement, but if it is I expect its only splitting-hairs ... |
|
|
|
stark8352
Starting Member
6 Posts |
Posted - 2011-09-22 : 15:22:20
|
| Kristen,Thank you for the alternative solution to my problem, I will bear that one in mind. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-22 : 19:30:32
|
Glad it worked out for you.  |
|
|
|
|
|
|
|
|
Problem with Division by Zero