PRINT NUMBER WITH DECIMAL PLACES

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

SQL Server 2000 Forums

Transact-SQL (2000)

PRINT NUMBER WITH DECIMAL PLACES

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!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources