Solved

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

Posted on 2014-10-16
11
162 Views
Last Modified: 2014-10-16
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.
0
Comment
Question by:ryann
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40385536
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40385544
>> 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
 

Author Comment

by:ryann
ID: 40385563
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40385565
SELECT  top 200
          [Vendor Time Spread]
   FROM [Ltiv4].[dbo].[LtiRptNCMICCommissionVW]
ORDER BY [Vendor Time Spread] DESC


Presumably the largest values are the issue.
0
 

Author Comment

by:ryann
ID: 40385583
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40385590
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
 

Author Comment

by:ryann
ID: 40385599
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40385605
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
 

Author Comment

by:ryann
ID: 40385609
Thank you Scott but how do I see what is in the column without getting the error.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40385672
>>"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
 

Author Closing Comment

by:ryann
ID: 40385769
THANKYOU! I was able to see what was in the column.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error "String or binary data would be truncated" in an update on a datatable 7 29
SQL Server Question 5 29
Update data using formula 22 23
recover sqlserver db 8 58
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now