sql query to see record that gives Arithmetic overflow error converting numeric to data type numeric.

I know the record and the column that is giving me this error. I want to see the contents of this record. How do I write a query to do this.
ryannAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just a thought:  It's kinda hard to answer questions about queries and error messages unless we can see them.
Please post the code of the query you're working with, and the error message received.

>overflow error converting numeric to data type numeric.
Converting numeric to numeric?
0
Scott PletcherSenior DBACommented:
>> I know the record and the column that is giving me this error. <<

Is there some type of key in the record?

You could also query the table by that column value, although that might take much longer and could return many rows.

SELECT * FROM dbo.table_name WHERE column = <column_value_that_is_failing_conversion>


>> 
>overflow error converting numeric to data type numeric.
 Converting numeric to numeric?
<<
Very possible if you're converting from, say, numeric(11,2) to numeric(9,2).
0
ryannAuthor Commented:
I changed the query to only select the field that is giving me the error.

SELECT  top 200
         [Vendor Time Spread]
  FROM [Ltiv4].[dbo].[LtiRptNCMICCommissionVW]



Partial results of this query
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott PletcherSenior DBACommented:
SELECT  top 200
          [Vendor Time Spread]
   FROM [Ltiv4].[dbo].[LtiRptNCMICCommissionVW]
ORDER BY [Vendor Time Spread] DESC


Presumably the largest values are the issue.
0
ryannAuthor Commented:
The record giving the error is 2397. I would like to know how to see what is in this column for that record or how I can fix it. Thanks. This is the error:

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
By any chance is LtiRptNCMICCommissionVW a view or table-valued function, and if so please copy-paste the SQL behind it into this question.
0
ryannAuthor Commented:
It is a view. The sql behind it is pages long.  All I am looking for is how  to fix the column which has the bad data or a way to look at what is in it.  This is meant as a general question on how a person would do this.

This is the line of code which references the field. Most of the records are fine but some bad data got into the table somehow.

                  ,ISNULL(CAST(CAST(DATEDIFF(DD,  VendorUDF.value_date,History.GLPostingDate)  AS NUMERIC(8,0)) / 364 AS  NUMERIC(4,2)),0) AS [Vendor Time Spread]
0
Scott PletcherSenior DBACommented:
That will handle only 99.99 days -- anything at or over 100 days will fail.

You could add a CASE statement, or WHERE condition, to prevent that.
0
ryannAuthor Commented:
Thank you Scott but how do I see what is in the column without getting the error.
0
PortletPaulfreelancerCommented:
>>"how do I see what is in the column without getting the error."

 ,ISNULL(CAST(CAST(DATEDIFF(DD,  VendorUDF.value_date,History.GLPostingDate)  AS NUMERIC(8,0)) / 364 AS
  NUMERIC(        4            ,2)),0) AS [Vendor Time Spread]
                           ^^^
increase that 4 to something bigger, e.g 8
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ryannAuthor Commented:
THANKYOU! I was able to see what was in the column.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.