August 1, 2008 at 12:13 am
Both the correct answer and an error is returned:
Msg 232, Level 16, State 3, Line 2
Arithmetic overflow error for type int, value = 2147483648.000000.
August 1, 2008 at 1:35 am
well i got only error.
safe way is select power(2.0,31)
August 1, 2008 at 6:34 am
I got what JWOL got. SQL Server reported both the overflow error and the correct answer. A bit schizophrenic perhaps? 😉
This serves to show that one needs to be cognizant of data types and how they work. Learning is fun-damental. 😀
August 1, 2008 at 6:38 am
A bit unnerving to answer this one... is it for Windows SQL Server 7.0, 2000, 2005, 2008 - did they change anything to allow it to work in some cases and not in others? Would it be a different answer if I set my options to be non-backward compatible to version 8 (from version 9). And so on. VERY pleased that this question was not a trick question.
Jamie
August 1, 2008 at 7:05 am
Yes, the error message includes the correct answer, but the SELECT statement didn't return anything.
August 1, 2008 at 8:57 am
This will also work:
SELECT power(cast(2 as bigint),31)
August 1, 2008 at 9:01 am
Strangely enough, the answer returns in the error message.
August 1, 2008 at 10:57 am
If the correct answer is returned in the error message why not just return the answer and gorget about the error. Obviously the user intended to return an answer without needing to specifically declare a type. What's the point of knowing the answer and returning an error? When I program an application I try to anticipate what the user wants and give it to him/her. I don't chastise them for asking incorrectly.
August 1, 2008 at 11:34 am
The correct answer was returned with the error message but exceeded the maximum value allowed for that data type. That is why the result had to be an error.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 1, 2008 at 11:46 am
With all due respect, I failed to get my point accross. If I use power(2.0,31) I don't get an error. SQL Server should have been intelligent enough to anticipate my needs and use a float or bigint, etc. Software should be 'smart' not sarcastic. It knew the answer but insisted I change something to get it.
August 1, 2008 at 11:53 am
I'm sorry abinder but I think you're not considering a very important point.
It would not be appropriate for SQL to change the data type of the answer unless it was specifically told to do so. If you are just running a query in query analyzer where you just want to see the result then it's no big deal, but what if the results are to be passed to another process, or stored in a table, that cannot handle the "changed" data type?
You need to look at the full picture before saying that SQL Server should .....
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 1, 2008 at 12:32 pm
The int data type is the default data type for this statement. The default could just as well have been bigint. If I use, or even anticipate using, power(2,31) I obviously intended the statement to handle it and would have typed any resultant variable or table to handle it. I guess I'm mostly mystified by an error statement that returns an error AND the correct answer at the same time. It almost seems like a programmer was trying to have a little fun.
Anyway, I apprecaite your point of view. The long and short of it is whether I get an error at Power(2,31) or if it didn't give me the error and I used the result sometime later in an int datatype, I would have gotten an error either way.
August 1, 2008 at 1:51 pm
I looked at it: 2 to the 31st power is
2147483648---ackkkk sorry wrong, didn't even think of datatype. I suppose I could have run it in the query analyser and got Arithmetic overflow error for type int, value = 2147483648.000000. But seems like cheating.
Bet I pay attension to datatype limits in the future
August 4, 2008 at 2:06 am
It is giving an error message with the answer. 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply