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.
| Author |
Topic |
|
PATPC999
Starting Member
8 Posts |
 Posted - 2010-11-25 : 09:16:17
|
Im am trying to PRINT @AVG
DECLARE @NUMBERONE AS NUMARIC
DECLARE @NUMBERTWO AS NUMARIC
DECLARE @AVG AS DECMILE(3,1)
SET = @NUMBERONE = 4854
SET = @NUMBERTWO = 60
SET = @AVG = CONVERT(DECIMAL(3,1), @NUMBERONE/@NUMBERTWO)
PRINT @AVG
When I do this I get
Arithmetic overflow error converting numeric to data type numeric.
and it PRINTs 0.00
When I PRINT @AVG I want to see 80.9
Help!!!!! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 09:34:41
|
A number of typos
Don't know if that's causing the issue but when I run the same thing I get 80.9
Suspect this is not really what you are running - check the values and datatypes - especially @avg.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
PATPC999
Starting Member
8 Posts |
Posted - 2010-11-25 : 09:38:13
|
Yes I see NUMARIC it should read NUMERIC
It is a right in the real code.
still getting error
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 09:53:13
|
I've tried it on a v2000 server and it works ok.
can you post the result of
select @NUMBERONE/@NUMBERTWO
and
select @AVG = 99.9
select @AVG
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
PATPC999
Starting Member
8 Posts |
Posted - 2010-11-25 : 10:10:08
|
DECLARE @CREDITSUM AS INT
DECLARE @TOTALGRADES AS INT
DECLARE @AVG AS DECIMAL(3,1)
SET @TOTALGRADES = 4854
SET @CREDITSUM = 60
SET @AVG = CONVERT(DECIMAL(3,2),@TOTALGRADES/@CREDITSUM)
PRINT @AVG
I am new to this as you can tell
when I run this code it displays 0.0 instead of 80.9
Also error message
Server: Msg 8115, Level 16, State 8, Line 101
Arithmetic overflow error converting numeric to data type numeric.
Not sure where I am going wrong to get the decimal point to display
if I write
SET @AVG = 4854/60
PRINT CAST(@AVG AS CHAR(5)) I get 80 instead of 80.9 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 10:13:51
|
You are doing integer arithmetic so the result will be truncated to an integer.
try
declare @AVG decimal(4,2)
SET @AVG = CONVERT(DECIMAL(3,2),1.0*@TOTALGRADES/@CREDITSUM)
decimal(3,2) is 3 digits with 2 decimal places eg 8.09 - 80.9 will not fit in it.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
PATPC999
Starting Member
8 Posts |
Posted - 2010-11-25 : 10:34:54
|
I got it to work!!!
Declare all three as DECIMAL
and
SET @AVG = 1.0*(@TOTALGRADES/CREDITSSUM)
PRINT @AVG
I get 80.90
Thanks so much!!!!!!!!!!!!!! |
|
|
|
|
|
|
PRINT NUMBER WITH DECIMAL PLACES