If the player didn't appear in the game at all I think you'll get a null somewhere, also it is possible that SQL evaluates all possibilities in the case statement.
You might be better served by using a sub query to total your at bats, then join that to your main query SELECT p.*, COALESCE(ab.AtBats,0) AtBats From Player p LEFT JOIN (SELECT PlayerID, Count(PlayerID) AtBats FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) GROUP BY PlayerID) ab on p.PlayerID = ab.PlayerID On 3/31/06, bh0526 <[EMAIL PROTECTED]> wrote: > Hi all, > > > > I am having a terrible time with a stored procedure using SQL Server > 2005. I think it has something to do with a divide by zero issue. > Here is my sproc: > > > > ALTER > > PROCEDURE [dbo].[GetPlayers] > @TeamID > > int > AS > > SELECT PlayerID, > > TeamID, > > FirstName > > , > LastName > > , > FirstName > > + ' ' + LastName As Name, > "SPct" > > = > CASE > > (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) > AND GameDetail.PlayerID = Player.PlayerID) > WHEN > > 0 THEN 0 > ELSE > > Cast > > ( > > Cast > > ( ( > > (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = > Player.PlayerID AND AtBatID = '1') + > > (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = > Player.PlayerID AND AtBatID = '2') + > > (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = > Player.PlayerID AND AtBatID = '3') + > > (SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = > Player.PlayerID AND AtBatID = '4') > > / > > (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) > AND GameDetail.PlayerID = Player.PlayerID > > ) * 1.0 > > ) As int > > ) As Decimal(4,3)) > End > > FROM > > Player > WHERE > > TeamID = @TeamID > ORDER > > BY FirstName, LastName > > > Here is the error I get when I run this: > > Msg 8115, Level 16, State 8, Procedure GetPlayers, Line 9 > > Arithmetic overflow error converting int to data type numeric. > > So the results should look like: .333 or .500 or .000 > > > > It's driving me crazy. I have tried everything but cannot get the > desired results. I'm hoping someone can help me. > > > > Thanks, > > Bob > > > > > > > > > Yahoo! Groups Links > > > > > > > -- Dean Fiala Very Practical Software, Inc Now with Blogging... http://www.vpsw.com/blogbaby Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNet2/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
