Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error in query

Posted on 2008-10-28
13
Medium Priority
?
478 Views
Last Modified: 2013-11-06
i have this query that has been working fine for days. all the sudden i get this error:

Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

how do i solve this? thank you experts.
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(TotalActual) AS TotalActual, SUM(TotalTarget) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
Comment
Question by:sassy168
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 15

Expert Comment

by:Faiga Diegel
ID: 22824447
Is the TotalActual and TotalTarget integers?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824453
You might have some non-numeric values in your data.

Try this:

SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
 
LVL 8

Expert Comment

by:k_rasuri
ID: 22824458
It could be the problem with the incoming source data..can you show some sample of the source data
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:sassy168
ID: 22824519
yes, totalActual totalTarget are int. I ran your example through query analyzer, i  got this but still with error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

(4 row(s) affected)



NULL      NULL      NULL      0      0
1      2008      9      1662956      8738262
1      2008      10      575717568      1152717888
2      2008      9      1590907      8472954
0
 

Author Comment

by:sassy168
ID: 22824550
i think i figured out why, some of the data have null.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824584
Seems like you have some data with NULL Board_ID and Run_Date.  I have seen where once a query gets some valid data back from a column it will assign a datatype to that column and so subsquent data is constrainted against that type therefore you may be getting message due to fact that NULL can't be converted to INT for year, month or board_id.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824608
Since the date is the one in formula, try this (should know data type of board_id from database):
If you don't want this to default to a date like current date, then just put the ISNULL on the outside of the DatePart formula and set value to 0.
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, ISNULL(Run_Date, getdate())) AS year, DATEPART(m, ISNULL(Run_Date, getdate())) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824646
If you still get error, try ISNULL(Board_ID, 0).
If that works, you can take off the ISNULL off the Run_Date portion and test and see.
Alternatively, since the NULL rows aren't going to add value with all the defaulting you can just do this:
SELECT     TOP 100 PERCENT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month, SUM(CASE ISNUMERIC(TotalActual) WHEN 0 THEN 0 ELSE TotalActual END) AS TotalActual, SUM(CASE ISNUMERIC(TotalTarget) WHEN 0 THEN 0 ELSE TotalTarget END) 
                      AS TotalTarget
FROM         dbo.TB_Cummulative
WHERE Board_ID IS NOT NULL
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID

Open in new window

0
 

Author Comment

by:sassy168
ID: 22824744
okay i remove the record with null values, i still get this:

Arithmetic overflow error converting expression to data type int.

is it possible that the number is too big? here is one of the bigger numbers, 1152717888



0
 

Author Comment

by:sassy168
ID: 22824798
i used this and it worked,

SELECT Board_ID, DATEPART(yyyy, Run_Date) AS year, DATEPART(m, Run_Date) AS month,
SUM(cast(TotalActual as bigint)) as TotalActual, sum(cast(TotalTarget as bigint)) as TotalTarget
FROM         dbo.TB_Cummulative
GROUP BY DATEPART(m, Run_Date), DATEPART(yyyy, Run_Date), Board_ID
ORDER BY Board_ID


now should i just change the data time to big int instead?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22824845
Yes, BIGINT will work as it can handle a number of that size.  If you data will hold values that high, YES!

http://msdn.microsoft.com/en-us/library/ms187745.aspx
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824852
The alternative if this is financial data is to use MONEY:
http://msdn.microsoft.com/en-us/library/ms179882.aspx

Same effect, you need a variable big enough to store the highest value.
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22859867
Try to replace in your query :
WHEN 0 THEN 0 ELSE TotalTarget END
with:
WHEN null THEN 0 ELSE TotalTarget END
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SEO can be a real minefield to navigate, but there are three simple ways to up your SEO game just be re-assessing your content output.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question