Query Error

Hi all,
I am getting the following error when running a crosstab-like query on my
SQL Server: "Arithmetic overflow error converting expression to data type
int." I have been able to run this query before w/o this error before.
Does anyone know why this error is happening and how to fix?
Here is my query...
SELECT TOP 100 PERCENT dbo.[File Structure].Source,
SUM(CASE Month(RptDate) WHEN 1 THEN PageViews ELSE 0 END) AS Jan,
SUM(CASE Month(RptDate) WHEN 2 THEN PageViews ELSE 0 END) AS Feb,
SUM(CASE Month(RptDate) WHEN 3 THEN PageViews ELSE 0 END) AS Mar,
SUM(CASE Month(RptDate) WHEN 4 THEN PageViews ELSE 0 END) AS Apr,
SUM(CASE Month(RptDate) WHEN 5 THEN PageViews ELSE 0 END) AS May,
SUM(CASE Month(RptDate) WHEN 6 THEN PageViews ELSE 0 END) AS Jun,
SUM(CASE Month(RptDate) WHEN 7 THEN PageViews ELSE 0 END) AS Jul,
SUM(CASE Month(RptDate) WHEN 8 THEN PageViews ELSE 0 END) AS Aug,
SUM(CASE Month(RptDate) WHEN 9 THEN PageViews ELSE 0 END) AS Sep,
SUM(CASE Month(RptDate) WHEN 10 THEN PageViews ELSE 0 END) AS Oct,
SUM(CASE Month(RptDate) WHEN 11 THEN PageViews ELSE 0 END) AS Nov,
SUM(CASE Month(RptDate) WHEN 12 THEN PageViews ELSE 0 END) AS Dec
FROM dbo.Data INNER JOIN dbo.[File Structure] ON dbo.Data.SourceID = dbo.[File Structure].SourceID
WHERE (YEAR(dbo.Data.RptDate) = 2007) AND (dbo.[File Structure].SourceID <= 15 OR
dbo.[File Structure].SourceID = 62 or (dbo.[File Structure].SourceID
BETWEEN 80 AND 84))
GROUP BY Source
ORDER BY Source
Thanks for your help! :)
RobMy guess is that dbo.[File Structure].SourceID is declared as a
character string rather than a number, and contains at least one
non-numeric character. Try running:
SELECT SourceID
FROM dbo.[File Structure]
WHERE ISNUMERIC(SourceID) <> 1
Roy Harvey
Beacon Falls, CT
On Tue, 11 Sep 2007 08:45:49 -0500, "Rob" <no@.email.com> wrote:
>Hi all,
>I am getting the following error when running a crosstab-like query on my
>SQL Server: "Arithmetic overflow error converting expression to data type
>int." I have been able to run this query before w/o this error before.
>Does anyone know why this error is happening and how to fix?
>Here is my query...
>SELECT TOP 100 PERCENT dbo.[File Structure].Source,
> SUM(CASE Month(RptDate) WHEN 1 THEN PageViews ELSE 0 END) AS Jan,
> SUM(CASE Month(RptDate) WHEN 2 THEN PageViews ELSE 0 END) AS Feb,
> SUM(CASE Month(RptDate) WHEN 3 THEN PageViews ELSE 0 END) AS Mar,
> SUM(CASE Month(RptDate) WHEN 4 THEN PageViews ELSE 0 END) AS Apr,
> SUM(CASE Month(RptDate) WHEN 5 THEN PageViews ELSE 0 END) AS May,
> SUM(CASE Month(RptDate) WHEN 6 THEN PageViews ELSE 0 END) AS Jun,
> SUM(CASE Month(RptDate) WHEN 7 THEN PageViews ELSE 0 END) AS Jul,
> SUM(CASE Month(RptDate) WHEN 8 THEN PageViews ELSE 0 END) AS Aug,
> SUM(CASE Month(RptDate) WHEN 9 THEN PageViews ELSE 0 END) AS Sep,
> SUM(CASE Month(RptDate) WHEN 10 THEN PageViews ELSE 0 END) AS Oct,
> SUM(CASE Month(RptDate) WHEN 11 THEN PageViews ELSE 0 END) AS Nov,
> SUM(CASE Month(RptDate) WHEN 12 THEN PageViews ELSE 0 END) AS Dec
>FROM dbo.Data INNER JOIN dbo.[File Structure] ON dbo.Data.SourceID =>dbo.[File Structure].SourceID
>WHERE (YEAR(dbo.Data.RptDate) = 2007) AND (dbo.[File Structure].SourceID <=>15 OR
> dbo.[File Structure].SourceID = 62 or (dbo.[File Structure].SourceID
>BETWEEN 80 AND 84))
>GROUP BY Source
>ORDER BY Source
>Thanks for your help! :)
>Rob
>|||Rob
What if you put the qutations as
dbo.[File Structure].SourceID = '62' or (dbo.[File Structure].SourceID
BETWEEN '80' AND '84'))
"Rob" <no@.email.com> wrote in message
news:Or4thlH9HHA.1900@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I am getting the following error when running a crosstab-like query on my
> SQL Server: "Arithmetic overflow error converting expression to data type
> int." I have been able to run this query before w/o this error before.
> Does anyone know why this error is happening and how to fix?
> Here is my query...
> SELECT TOP 100 PERCENT dbo.[File Structure].Source,
> SUM(CASE Month(RptDate) WHEN 1 THEN PageViews ELSE 0 END) AS Jan,
> SUM(CASE Month(RptDate) WHEN 2 THEN PageViews ELSE 0 END) AS Feb,
> SUM(CASE Month(RptDate) WHEN 3 THEN PageViews ELSE 0 END) AS Mar,
> SUM(CASE Month(RptDate) WHEN 4 THEN PageViews ELSE 0 END) AS Apr,
> SUM(CASE Month(RptDate) WHEN 5 THEN PageViews ELSE 0 END) AS May,
> SUM(CASE Month(RptDate) WHEN 6 THEN PageViews ELSE 0 END) AS Jun,
> SUM(CASE Month(RptDate) WHEN 7 THEN PageViews ELSE 0 END) AS Jul,
> SUM(CASE Month(RptDate) WHEN 8 THEN PageViews ELSE 0 END) AS Aug,
> SUM(CASE Month(RptDate) WHEN 9 THEN PageViews ELSE 0 END) AS Sep,
> SUM(CASE Month(RptDate) WHEN 10 THEN PageViews ELSE 0 END) AS Oct,
> SUM(CASE Month(RptDate) WHEN 11 THEN PageViews ELSE 0 END) AS Nov,
> SUM(CASE Month(RptDate) WHEN 12 THEN PageViews ELSE 0 END) AS Dec
> FROM dbo.Data INNER JOIN dbo.[File Structure] ON dbo.Data.SourceID => dbo.[File Structure].SourceID
> WHERE (YEAR(dbo.Data.RptDate) = 2007) AND (dbo.[File Structure].SourceID
> <= 15 OR
> dbo.[File Structure].SourceID = 62 or (dbo.[File Structure].SourceID
> BETWEEN 80 AND 84))
> GROUP BY Source
> ORDER BY Source
> Thanks for your help! :)
> Rob
>|||Hi everyone,
I figured out the problem... PageViews column was an int data type, which is
limited to hold values between +- 2^31-1. The sum of all Aug-07 Pageviews
exceeded this limit, which resulted in an error. Thanks for the help! :)
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsRDI7H9HHA.4784@.TK2MSFTNGP05.phx.gbl...
> Rob
> What if you put the qutations as
> dbo.[File Structure].SourceID = '62' or (dbo.[File
> Structure].SourceID
> BETWEEN '80' AND '84'))
> "Rob" <no@.email.com> wrote in message
> news:Or4thlH9HHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I am getting the following error when running a crosstab-like query on my
>> SQL Server: "Arithmetic overflow error converting expression to data type
>> int." I have been able to run this query before w/o this error before.
>> Does anyone know why this error is happening and how to fix?
>> Here is my query...
>> SELECT TOP 100 PERCENT dbo.[File Structure].Source,
>> SUM(CASE Month(RptDate) WHEN 1 THEN PageViews ELSE 0 END) AS Jan,
>> SUM(CASE Month(RptDate) WHEN 2 THEN PageViews ELSE 0 END) AS Feb,
>> SUM(CASE Month(RptDate) WHEN 3 THEN PageViews ELSE 0 END) AS Mar,
>> SUM(CASE Month(RptDate) WHEN 4 THEN PageViews ELSE 0 END) AS Apr,
>> SUM(CASE Month(RptDate) WHEN 5 THEN PageViews ELSE 0 END) AS May,
>> SUM(CASE Month(RptDate) WHEN 6 THEN PageViews ELSE 0 END) AS Jun,
>> SUM(CASE Month(RptDate) WHEN 7 THEN PageViews ELSE 0 END) AS Jul,
>> SUM(CASE Month(RptDate) WHEN 8 THEN PageViews ELSE 0 END) AS Aug,
>> SUM(CASE Month(RptDate) WHEN 9 THEN PageViews ELSE 0 END) AS Sep,
>> SUM(CASE Month(RptDate) WHEN 10 THEN PageViews ELSE 0 END) AS Oct,
>> SUM(CASE Month(RptDate) WHEN 11 THEN PageViews ELSE 0 END) AS Nov,
>> SUM(CASE Month(RptDate) WHEN 12 THEN PageViews ELSE 0 END) AS Dec
>> FROM dbo.Data INNER JOIN dbo.[File Structure] ON dbo.Data.SourceID =>> dbo.[File Structure].SourceID
>> WHERE (YEAR(dbo.Data.RptDate) = 2007) AND (dbo.[File Structure].SourceID
>> <= 15 OR
>> dbo.[File Structure].SourceID = 62 or (dbo.[File Structure].SourceID
>> BETWEEN 80 AND 84))
>> GROUP BY Source
>> ORDER BY Source
>> Thanks for your help! :)
>> Rob
>