need help update query error Arithmetic overflow...

[DougP]

Server: Msg 8115, Level 16, State 6, Line 6
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated.

Can I just add something? 1.0 * does not work

declare @Qtr char(3),@startdt datetime,@etiology varchar(50)
set @Qtr ='4th'
set @startdt ='10/1/04'
set @etiology ='Diabetic Ulcer'

update IndEtioOutcomesCountsbyEtiology
set HealedWndPerc = convert(float,HealedWndCnt) / case when 
	(convert(float,(select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr)) = 0) then 
	1 else  convert(float,(select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr)) end

DougP, MCP, A+

 

[gmmastros]

Run this, and post the results back here.

Select Table_Name, 
       Column_Name, Numeric_Precision, Numeric_Scale
From   Information_schema.columns
Where  Table_Name = 'IndEtioOutcomesCountsbyEtiology'
       And Column_Name = 'HealedWndPerc'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DougP]

Table column precision scale
IndEtioOutcomesCountsbyEtiology HealedWndPerc 3 2


DougP, MCP, A+

 

[gmmastros]

Well, I can't be sure what is causing your problem because I don't know what the values are, but I suspect your precision and scale are causing the problem.

With Precision = 3 and scale = 2, you are limiting the acceptable range of numbers to -9.99 to 9.99

Precision dictates the total number of digits (3) and scale dictates the number of digits after the decimal point.

I would suggest you modify the field to accomodate larger numbers.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Copy/paste this to a blank query analyzer window.

Declare @data decimal(3,2)

Set @Data = -9.99
Set @Data = 9.99
Set @Data = 10    [green]-- This statement errors[/green]

When you run this, you will get an overflow error in Line 5 (Set @Data = 10).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DougP]

this only occurs with data in the year 2004
I can see the value is 0 for one of the rows.
I can't however make this select statement work to see everything. gives error
Server: Msg 107, Level 16, State 3, Line 5
The column prefix 'IndEtioOutcomesCountsbyEtiology' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 5
The column prefix 'IndEtioOutcomesCountsbyEtiology' does not match with a table name or alias name used in the query.

declare @Qtr char(3),@startdt datetime
set @Qtr ='4th'
set @startdt ='10/1/04'

select HealedWndCnt / case when 
	(select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr) = 0 then 
	1 else  (select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr) end

DougP, MCP, A+

 

[DougP]

FYI the column name HealedWndCnt with an 'N' between C and T is in table IndEtioOutcomesCountsbyEtiology
the column HealedWndCt with out the 'N' is in table IndEtioOutcomesCounts

so it is not a typo

DougP, MCP, A+

 

[DougP]

Ok I got this to work

declare @Qtr char(3),@startdt datetime,@etiology varchar(50)
set @Qtr ='4th'
set @startdt ='10/1/04'
set @etiology ='Diabetic Ulcer'
select HealedWndCnt, HealedWndCt, HealedWndCnt / case when 
	(select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr) = 0 then 
	1 else  (select HealedWndCt from IndEtioOutcomesCounts where 
	facid = IndEtioOutcomesCountsbyEtiology.facid 
	and rptyear = DatePart(yyyy,@startdt) and rptqtr = @Qtr) end as a
from IndEtioOutcomesCounts,IndEtioOutcomesCountsbyEtiology

which gives these results partial results (it actually returns 9000 some rows
HealedWndCnt HealedWndCt a
0 59 0
0 69 0
0 87 0
0 35 0
0 49 0
0 21 0
0 123 0
0 97 0
0 81 0
0 117 0
0 84 0
0 2 0
0 29 0
0 0 0
0 86 0
0 2 0
0 111 0
0 76 0



DougP, MCP, A+

 

[DougP]

sorry to keep going on
I see further down there are nulls for a
5 59 NULL
5 69 NULL
5 87 NULL
5 35 NULL
5 49 NULL
5 21 NULL
5 123 NULL
5 97 NULL
5 81 NULL
5 117 NULL
5 84 NULL
5 2 NULL
5 29 NULL
5 0 NULL
5 86 NULL
5 2 NULL
5 111 NULL
5 76 NULL
5 64 NULL
5 7 NULL
5 45 NULL
5 23 NULL
5 4 NULL
5 7 NULL


DougP, MCP, A+

 

[DougP]

Ok problem solved
I changed the column to decimal (5,2)

DougP, MCP, A+