Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-16
11
167 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

792 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