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 |
|
olay80
Yak Posting Veteran
62 Posts |
 Posted - 2005-07-28 : 08:14:55
|
| hey guys,i'm using the following sql select:SELECT SQLMain.RepGroupShortName, SQLMain.RepGroupDivisionName, SQLMain.LastName+ ', ' + SQLMain.FirstName AS 'RepName', SQLMain.EmployeeNumber, SQLMain.RepID, CAST(SUM(SQLMain.WSDeviceTime) AS bigint) AS 'WSDeviceTime', CAST(SUM(DateDiff(SECOND,SQLMain.CallStartDateTime,SQLMain.CallEndDateTime)) AS bigint) AS 'SumCallTime', COUNT(Distinct SQLMain.StoreID)AS 'StoreCnt', Count(Distinct SQLMain.CallSID) AS 'CallCnt', MIN(SQLMain.CallStartDateTime)AS 'EarliestStart', MAX(SQLMain.CallStartDateTime)AS 'latestStart', SUM(SQLMain.WSCnt) AS 'WSCnt', SUM(SQLMain.TotalSheetScore) AS 'TotalSheetScore', COUNT(distinct SQLMain.ConnectionSID)AS 'SyncCnt' , SUM(SQLMain.QuestPics) AS CallPicsand it's giving me the following error:Arithmetic overflow error converting expression to data type int.Warning: Null value is eliminated by an aggregate or other SET operation.i'm pretty sure it's from the CAST to bigint i used in the select cause when i deleted these two rows it worked fine.thanx,Oliver |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 08:27:43
|
| SUM won't promote int to BigInt (assuming that WSDeviceTime is an int, for example), so you'd have to change the code to get bigint sums':SUM(CAST(SQLMain.WSDeviceTime AS bigint)) AS 'WSDeviceTime',Kristen |
 |
|
|
olay80
Yak Posting Veteran
62 Posts |
Posted - 2005-07-29 : 10:48:29
|
| thanx alot it worked |
|
|
|
|
|
|
|
|
Arithmetic overflow error