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 |
|
brandonl
Yak Posting Veteran
58 Posts |
 Posted - 2002-10-04 : 10:21:35
|
| I have the following SELECT statement:SELECT P_Passing.Yards, P_Passing.Attempts, (P_Passing.Yards/P_Passing.Attempts) AS YardsPerAttempt FROM P_PassingSome of the yards are 0 and some of the attempts are 0. I'm getting a divide by zero error on these records (obviously). How can I make the statement ignore the error and just report the YardsPerAttempt as 0 for the one's that fail? I've tried wrapping the division in ISNULL(), but it still fails.Thanks!~BrandonL |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-04 : 10:25:53
|
| SELECT Yards, Attempts, CASE Attempts WHEN 0 THEN 0 ELSE (Yards/Attempts) END AS YardsPerAttempt FROM P_PassingNote that if Yards or Attempts are integer columns, you'll get integer division (no decimal) when you divide them. You can use CONVERT or CAST to change them to float, and then divide them. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-04 : 10:26:02
|
| case when P_Passing.Attempts = 0 then 0 else P_Passing.Yards/P_Passing.Attempts end==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-10-04 : 10:29:36
|
| You could do something like this:SELECT P_Passing.Yards, P_Passing.Attempts, CASE WHEN P_Passing.Yards = 0 OR P_Passing.Attempts = 0 THEN 0 ELSE (P_Passing.Yards/P_Passing.Attempts) END AS YardsPerAttemptFROM P_Passing You could also eliminate 0s in the WHERE clause.Another approach would be to control the behavior using some SET options. Read the page titled "Behavior if Both ARITHABORT and ARITHIGNORE Are Set ON" in SQL Server Books Online.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
brandonl
Yak Posting Veteran
58 Posts |
Posted - 2002-10-04 : 10:30:25
|
| Excellent! That worked, thanks guys!~BrandonL |
|
|
|
|
|
|
Divide by Zero error in select