Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-16
11
Medium Priority
?
178 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 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 70

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 70

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 70

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

824 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