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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 arithmetic overflow error

Author  Topic 

SherinEliz
Starting Member

5 Posts

Posted - 2006-09-18 : 02:17:09
i have an sql query that goes like this:

select CategoryID & power(2,x) from CategoryDetails

where CategoryID can range upto 15 digits eg: 137652435487090
x is the result of a formulae and can range upto 2 digits eg:95

CategoryID is the current category ID.
Number resulting from the formulae is the subcategory ID of the current categoryID.

I am getting error Arithmetic overflow error for datatype bigint.
what datatype shd I use to resolve the error or is there any way out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 02:36:04
select 1.0 * CategoryID & power(2.0,x) from CategoryDetails


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SherinEliz
Starting Member

5 Posts

Posted - 2006-09-18 : 02:47:33
syntax error

invalid operator for data type. operator equals boolean AND type equals numeric
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 02:52:21
What datatype does CategoryID have?
What datatype does x have?

What is the purpose of doing binary algorithm on the variables? Are you simply trying to concatenate the two values?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 02:54:18
SELECT CONVERT(VARCHAR, CategoryID) + ' ' + CONVERT(VARCHAR(1000), POWER(2.0, x))


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:58:58
quote:
Originally posted by SherinEliz

syntax error

invalid operator for data type. operator equals boolean AND type equals numeric



The error occurs because result of Power(2, x) is too big to be converted into int or bigint and since you can't apply & operator on numeric data type, you are getting the error!!


Note:

power(2.0, 95) = 39614081257132169000000000000.0
Max. Range of BIGINT = 9223372036854775807

Way too less...


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SherinEliz
Starting Member

5 Posts

Posted - 2006-09-18 : 03:02:31
categoryID and x has the datatype bigint
no, i am not just concatenating the 2 results. performing bitwise & .
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 03:31:40
What is it? Concatenating or bitwise AND?
This is not the same operation...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SherinEliz
Starting Member

5 Posts

Posted - 2006-09-18 : 03:59:38
not concatenating. bitwise &
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 04:16:13
You must make a function of this. The number POWER(2, 95) is too large to handle for any SQL Server integer data types.

Dare I ask why this operation is made? Maybe there is other ways to accomplish your task?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -