Execution Error Message - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Execution Error Message

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-29 : 14:02:49

This is really strange to me. I am trying to run this report. I am able to run it from 01-01-2007 through 10-01-2007, but when I go to run it from 01-01-2007 throug 12-31-2007, I get this error messge below. I don't understand, if you can help let me know. Thanks!

Error Source: Net SQLClient Data Provider
Error Message:Arithmetic overflow error converting numeric to data type numeric.

Here is my SQL below, if that helps to look at.

SELECT DISTINCT
clm_wkpct, clm_1a, clm_12a, clm_12b, clm_55d, clm_clir, clm_65a, clm_medb2, clm_tchg, clm_base, clm_stades, clm_prod, clm_nego, clm_sppo,
clm_1e, Note, Clm_Att1, AccessFeeFinal, CLM_ATT2, CLM_ATT3, ACCESSFEEIMPACT, MAS90#, clm_meda4 AS OriginalAllow, CLM_H30 AS Adjusted,
clm_id1, CONVERT(CHAR(10), clm_rcvd, 110) AS daterecieved, CONVERT(CHAR(10), CLM_DOUT, 110) AS dateclosed,
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
FROM vw_Claims_Settlement_Rptdata_SharePoint_NO_DISCOUNT
WHERE (CLM_DOUT >= @BottomDate) AND (CLM_DOUT <= @TopDate)

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 14:41:59

Check your values in the clm_sppo and clm_tchg columns for data between 10/1 and 12/31. You've cased to a decimal(4,2), which means you can only have a value as high as 99.99. I'd guess is that something is coming in at 100%.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-29 : 14:43:41

Thank you!

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-29 : 15:15:29

How can I change it so it pulls other ones that are coming in past that?

quote:
Originally posted by tprupsis

Check your values in the clm_sppo and clm_tchg columns for data between 10/1 and 12/31. You've cased to a decimal(4,2), which means you can only have a value as high as 99.99. I'd guess is that something is coming in at 100%.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-29 : 15:16:04

I mean pass that.

quote:
Originally posted by werhardt

How can I change it so it pulls other ones that are coming in past that?

quote:
Originally posted by tprupsis

Check your values in the clm_sppo and clm_tchg columns for data between 10/1 and 12/31. You've cased to a decimal(4,2), which means you can only have a value as high as 99.99. I'd guess is that something is coming in at 100%.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 15:49:26

Try changing your cast to decimal(5,2).

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources