Solved

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

Posted on 2014-10-16
11
174 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
[X]
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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 66

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 66

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
Viewers will learn how the fundamental information of how to create a table.

617 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