January 21, 2003 at 8:47 am
Hi All,
I'm trying to make the following statement to run (Conversion of 8KB pages in MB) but it gaves me an arithmetic overflow for very big databases (some gig's)
SELECT *8192/1048576 AS SizeMB FROM sysfiles
Error : Arithmetic overflow error converting expression to data type int.
Can please someone help or give a tip to make it run ? Thanks in advance
Regards
Igor
January 21, 2003 at 8:52 am
Wrap each numeric part inside a cast as bigint statement. That should give you a few trillion to play with.
select cast( as bitint) * cast(8192 as bigint) etc...
January 21, 2003 at 9:03 am
Thanks already for the answer received but I forgot to mention one important point : It' must be runnable on SQL 7.0 1 SQL 2000 (So bigint can't be used...)
January 21, 2003 at 9:37 am
Hmmm
Well that's going to be a bit of a problem then.
Basically you won't be able to do that calculation as is, as sql cannot calculate int values over the maximum (2,147,483,648).
Options then:
Could output the uncalculated values and have some third part app do the calcs.
If you need it to come straight out of SQL, two thoughts:
1. You could divide each of the multiplying values, by say 1000, so that the output fits into an int datatype, then cast into a varchar, and add the trailing 6 zeros onto the end.
The result would be less accurate but would will give you a result.
eg:
declare @a int
declare @b-2 int
declare @OutPut varchar(1000)
set @a = 5465446
set @b-2 = 343415341
set @output = cast(( select (@A / 1000) * (@B/1000)) as varchar(1000)) + '000000'
select @output
2. Create your own multiplication functions (however in SQL 7 these would have to be SP's with output parameters)
I have played with these in the past, but unfortunately don't have the code to hand, but basically involves passing two numbers as string values into a function, and then going back to basics and multiplying each individual part of the number like you learnt at school. You would also need an addition function to add up the resulting multiplied values. It takes a bit of work to get right, but at the end you have a function that can multiply any sized numbers.
January 21, 2003 at 10:10 am
Try casting into a value where the numbers can be larger such as numeric.
SELECT cast( as numeric(28,0))*cast(8192 as numeric(28,0))/cast(1048576 as numeric(28,0))AS SizeMB FROM sysfiles
January 21, 2003 at 10:15 am
Yep, that makes more sense. Ignore my answer!
Until this point I never realised that numeric allows greater calculating abilities than ints or bigints - doh!
January 21, 2003 at 10:36 am
Guys, you need to look beyond the problem to what he's trying to do. It's a conversion of # 8KB pages to MB. The final result will always be smaller than the input. Just convert the conversion into the number of 8KB chunks there are in a megabyte, which is 128. Therefore, the statement would be:
SELECT /128 AS SizeMB FROM sysfiles
Jay
Jay Madren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply