SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"

I'm still quite new to SQL, and so I may be missing something quite
obvious.  Apologies in advance if that's the case.

I've included my query at the bottom of the message as it is quite long;
my problem is that I get "Arithmetic overflow error converting numeric
to data type numeric" when the first parameter to my COALESCE calls is
not NULL.  That is, the COALESCEs and the query work as I expect when
SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I
get the error.  I am also getting "Warning: Null value eliminated from
aggregate."

SELECT cust_vw_budget.BeginDate, cust_vw_budget.DeptNu,
cust_vw_budget.AcctNu AS ReportAcctNu, cust_vw_budget.Period,
SUM(cust_vw_budget.Budget) AS BudgetPeriod,cust_vw_actual.BeginDate,
cust_vw_actual.DeptNu, cust_vw_actual.AcctNu, cust_vw_actual.Period,
COALESCE(SUM(cust_vw_actual.Actual), 0.00) AS ActualPeriod,
COALESCE(SUM(cust_vw_actual.Actual), 0.00) - SUM(Budget) AS
VariancePeriod

FROM cust_vw_budget, cust_vw_actual

WHERE cust_vw_budget.BeginDate=3D'5/1/2002' AND
cust_vw_budget.BeginDate=3Dcust_vw_actual.BeginDate AND
cust_vw_budget.DeptNu=3D10 AND =
cust_vw_budget.DeptNu=3Dcust_vw_actual.DeptNu
AND cust_vw_budget.Period=3D10 AND
cust_vw_budget.Period=3Dcust_vw_actual.Period AND
cust_vw_budget.AcctNu*=3Dcust_vw_actual.AcctNu

GROUP BY cust_vw_budget.BeginDate, cust_vw_budget.DeptNu,
cust_vw_budget.AcctNu, cust_vw_budget.Period, cust_vw_actual.BeginDate,
cust_vw_actual.DeptNu, cust_vw_actual.AcctNu, cust_vw_actual.Period;
0
tim
4/25/2003 4:22:28 PM
perl.dbi.users 11098 articles. 1 followers. Follow

4 Replies
1661 Views

Similar Articles

[PageSpeed] 40

From:           	"Tim Howell" <tim@fefcful.org>
> I'm still quite new to SQL, and so I may be missing something quite
> obvious.  Apologies in advance if that's the case.
> 
> I've included my query at the bottom of the message as it is quite
> long; my problem is that I get "Arithmetic overflow error converting
> numeric to data type numeric" when the first parameter to my COALESCE
> calls is not NULL.  That is, the COALESCEs and the query work as I
> expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but
> if it isn't I get the error.  I am also getting "Warning: Null value
> eliminated from aggregate."

What's the type of the cust_vw_actual.Actual column?

To get rid of the warning you could replace the
	SUM(cust_vw_actual.Actual)
by
	SUM(COALESCE(cust_vw_actual.Actual,0.0))

and (unless the Budget column doesn't allow NULLs)
	SUM(Budget) 
by
	SUM(COALESCE(Budget, 0.0))

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
	-- Terry Pratchett in Sourcery

0
Jenda
4/25/2003 5:09:20 PM
The cust_vw_actual.Actual column is type Money.

--TWH

-----Original Message-----
From: Jenda Krynicky [mailto:Jenda@Krynicky.cz]=20
Sent: Friday, April 25, 2003 10:09 AM
To: DBI Users
Subject: Re: SQL Server Error: "Arithmetic overflow error converting
numeric to data type numeric"

<my original message snipped>

What's the type of the cust_vw_actual.Actual column?

To get rid of the warning you could replace the
	SUM(cust_vw_actual.Actual)
by
	SUM(COALESCE(cust_vw_actual.Actual,0.0))

and (unless the Budget column doesn't allow NULLs)
	SUM(Budget)=20
by
	SUM(COALESCE(Budget, 0.0))

Jenda
=3D=3D=3D=3D=3D Jenda@Krynicky.cz =3D=3D=3D http://Jenda.Krynicky.cz =
=3D=3D=3D=3D=3D
When it comes to wine, women and song, wizards are allowed=20
to get drunk and croon as much as they like.
	-- Terry Pratchett in Sourcery

0
tim
4/25/2003 6:40:23 PM
From: "Tim Howell" <tim@fefcful.org>
> The cust_vw_actual.Actual column is type Money.

And Budget? Maybe these two are of different types and the 
subtraction fails.
Try to comment out the 
	COALESCE(SUM(cust_vw_actual.Actual), 0.00) - SUM(Budget) AS
VariancePeriod

Does the error go away? If so put it back and try to convert one to 
the type of the other.

I thought maybe the
	 coalesce(sum(cust_vw_actual.Actual),0.0)
returns the error, but it doesn't seem to be the case. I tried to 
create a table with a Money column and tried similar query and it 
worked fine.

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
	-- Terry Pratchett in Sourcery

0
Jenda
4/25/2003 7:09:49 PM
I found my problem: it was the period I was using in 0.00

I changed it to simply 0 and it works fine.

--TWH

-----Original Message-----
From: Jenda Krynicky [mailto:Jenda@Krynicky.cz]=20
Sent: Friday, April 25, 2003 12:10 PM
To: dbi-users@perl.org
Subject: RE: SQL Server Error: "Arithmetic overflow error converting
numeric to data type numeric"


From: "Tim Howell" <tim@fefcful.org>
> The cust_vw_actual.Actual column is type Money.

And Budget? Maybe these two are of different types and the=20
subtraction fails.
Try to comment out the=20
	COALESCE(SUM(cust_vw_actual.Actual), 0.00) - SUM(Budget) AS
VariancePeriod

Does the error go away? If so put it back and try to convert one to=20
the type of the other.

I thought maybe the
	 coalesce(sum(cust_vw_actual.Actual),0.0)
returns the error, but it doesn't seem to be the case. I tried to=20
create a table with a Money column and tried similar query and it=20
worked fine.

Jenda
=3D=3D=3D=3D=3D Jenda@Krynicky.cz =3D=3D=3D http://Jenda.Krynicky.cz =
=3D=3D=3D=3D=3D
When it comes to wine, women and song, wizards are allowed=20
to get drunk and croon as much as they like.
	-- Terry Pratchett in Sourcery

0
tim
4/25/2003 10:49:51 PM
Reply:

Similar Artilces:

"Error converting data type nvarchar to numeric."
Hi  Im using the <asp:SqlDataSource to display a table in a datagrid. The data grid has AutoGenerateEditButton="True". I’m using this update command in the QslDataSource: UpdateCommand="UPDATE ShoeSize SET US = @US, Eur = @Eur, CM = @CM, Quant = @Quant, id = @id, UK = @UK  FROM ShoeSize WHERE IDroll = @IDroll" All the cells in the table is decimal(6,1) except ID, quant and IDroll witch are ints. But when I press update I get this error message: "Error converting data type nvarchar to numeric." I unde...

Help needed: arithmetic overflow error converting numeric to data type numeric
Hi, I have a SQL server 2005 db. The table structure (from big list of columns, only decimal columns I mentioned) below.  CREATE TABLE [Product].[MetricMeasures](    [MetricMeasureID] [int] IDENTITY(1,1) NOT NULL,    [ProductID] [int] NOT NULL,    [ProductSizeValue] [decimal](4, 2) NULL,    [ItemWeightValue] [decimal](3, 3) NULL,    [MasterCartonWeightValue] [decimal](3, 3) NULL,    [MasterCartonVolumeValue] [decimal](4, 4) NULL, CONSTRAINT [PK_MetricMeasures] PRIMARY KEY CLUSTERED ( &nbs...

Getting Server Error "Syntax error converting the nvarchar value 'Sonoma' to a column of data type int."
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource>  And the dropdown list's code:<asp:DropDownList ID="ddlPayrol...

Arithmetic overflow error converting float to data type numeric.
When this code executes, I am getting the error listed in the subject. The error is reported on the underlined and bold line. The values of the items are as follows: newTextName = SM1 newLat = 29.1878 newLong = 85.3417 newDescription = Test newCreator = John newCreate_Date = 9/14/2003 newDepth = 61 DropDownList2 = 7 'Get next Text_ID from Waypoint Table stmMaxSQL = "Select Max(WP_Text_ID) as WP_Text_ID From TBL_NM_Waypoint" objConn.Open() dadWaypoints = New SqlDataAdapter(stmMaxSQL, objConn) ...

Arithmetic overflow error converting float to data type numeric.
When this code executes, I am getting the error list in the subject. The values of the items are as follows: newTextName = SM1 newLat = 29.1878 newLong = 85.3417 newDescription = Test newCreator = John newCreate_Date = 9/14/2003 newDepth = 61 DropDownList2 = 7 'Get next Text_ID from Waypoint Table stmMaxSQL = "Select Max(WP_Text_ID) as WP_Text_ID From TBL_NM_Waypoint" objConn.Open() dadWaypoints = New SqlDataAdapter(stmMaxSQL, objConn) dadWaypoints.Fill(dstWaypoints, "Waypoints"...

Arithmetic overflow error converting varchar to data type numeric
Hello, i am getting "Arithmetic overflow error converting varchar to data type numeric"  while i am trying to cast the numeric value to varchar in SQL Server 2000(This is the Query  (cast(' + @Qty + ' as varchar(50)) If any knows plz help me, thanks.        "Arithmetic overflow error converting varchar to data type numeric" stands for error while converting varchar to numeric, and you say "trying to cast the numeric value to varchar", this is kind a confusing thing. Can you explain your problem more detailed?Nev...

upgrade error:"CONNECTIVITY ERROR:DB-Library: "SQL Server connection timed out."
PLEASE HELP OH WISE SYBASE GURUS! upgrading 11.9.2 to 12.0. unloaded software. rebooted server. selected "upgrade server" in Configure Sybase Servers window. typed in sa password. got "CONNECTIVITY ERROR: DB-Library: "SQL Server connection timed out." -- Chuck Boyce chuck_boyce@operamail.com http://www.geocities.com/chuck_boyce_jr/ Yahoo Messenger ID: chuck_boyce_jr http://messenger.yahoo.com AOL Instant Messenger ID: sg95m476 http://www.aol.com/aim/ ICQ Messenger ID: 41449031 http://www.icq.com ...

Getting error "Login failed for user ''. The user is not associated with a trusted SQL Server connection" on trying to authenticate a user
Hi, I created aspnetdb on Sql Server 2005 and modified my web.config LocalSqlServer connection string to connect to the Sql Server instead of mdf file on Sql Server. When i click Log In button on my login control i am getting the error "Login failed for user ''. The user is not associated with a trusted SQL Server connection" But when i bind a radio button list or any control to select from aspnet_users or roles table data is displayed fine without any login errors (i am using aspnetdbConnectionString given below for binding data). Why is it happening only when i authenticate but...

SQL Server 2005 error: "Incorrect syntax near "-" "
Hello, I have an application written in visual basic which connects to a SQL Server 2005In DB of SQL Server I have many tables. Table names can contain chars like: "-",  "\",  ".", etc. When I execute the instruction in visual basic sTableName= "Table-3" ' or ""Table-3"" or "Table_1" or sTableName= """" + "Table_1" + """"   -> """" = 4 quates (2 for string identify and two for writing in string a quate chr(34)->" strQu...

[wxperl-users] From whence "Wx::App Error" "Fatal error: exiting"?
This is a multi-part message in MIME format. ------_=_NextPart_001_01C48798.98A9B782 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I tried to send this message some months ago (early May), but for some reason at that time I was unable to send to the wxPerl mailing list. It seems to be working now, so . . . I'm stumbling around trying to find a single, fail-free method for exiting a running wxPerl app that may or may not have open frames. The Perl functions "exit" and "die" seem vulnerable to problem...

error "Login failed for user ''. The user is not associated with a trusted SQL Server connection. "
All the research I found dealing with this problem is that the solution is to set SQLserver to mix mode.  I have SQL server already set to mix mode. I am not sure what else to do. Has anybody run into this problem? my connection string: <connectionStrings> <add name="ConnectionString" connectionString="Data Source= server name ; Initial Catalog=FILESHAREDB.MDF; Integrated Security=false;"providerName="System.Data.SqlClient"/> </connectionStrings>   Server Error in '/SendItNow' Application.  ...

Whan can i do? make "string" data type convert to "window" data type, and use a "window" name to get it ancestor "window name", thanks! i use pb5
Rich, With a string window name, you can open a window of that type if you need to. You could, of course, query that window object for any attributes. Another option is to use the LibraryExport function to export the definition of the window which is specified by the string. You can then parse the resulting file to get information on the ancestor object. HTH Sanjiv. "Rich" <windows_ce@netease.com> wrote in message news:2Xsr1WW0AHA.178@forums.sybase.com... > > > ...

Error:"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance"
Firstly, Im using VWD2008 express and SQL Server 208 express. I added the database file to SQLServer and managed to create a connection in VWD but when I try to view a page I get the above error. Probably some dumb neewby thing Im doing wrong but if anyone can help Id appreciate it.   Thanks Adam. If you are using the classifieds starter kit, make sure the .zip file you used reads "final" not "beta" And you do not need sql server to run the kit. All you need is vwd and the framework installed on your computer.Undo what ever you did and just run the prog...

Error = Arithmetic overflow error converting expression to data type smalldatetim
  $exception {"Arithmetic overflow error converting expression to data type smalldatetime.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException} occurs here is my code protected void EmailSubmitBtn_Click(object sender, EventArgs e) { SqlDataSource NewsletterSqlDataSource = new SqlDataSource(); NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();   //Text version NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text; NewsletterS...

myDiv.style["display"]="none"; gives me an error while using a div in a User Control: Syntax error
Hi I have this part of my ascx user control that gives me a client side error : Syntax error <DIV id="dvRecurant"><asp:textbox id="txtFoisRecurant" tabIndex="7" runat="server" Height="20px"> </DIV> <script language="javascript">dvRecurant.style["display"]="none"</script>Thanks a lot, I appreciate your taking the time to help me. The the following client-side code: dvRecurant.style.display="none";/Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog Try:dvRecurant.style.display="none";Hope this helps. Home Is Where the...

Error on <form enctype="multipart/form-data" runat="server">
Hi to all... i have this problem which occured when i was uploading a picture into the SQL 2000. i am using VB script and Microsoft Visual Studio 2005... the error on"<pages enableEventValidation="true"/>"... can anyone help me??  i am using a master template for my webpage... everything work jus fine before i insert this "<form enctype="multipart/form-data" runat="server">" at the top of the page after "<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server&...

SQL Server 2005 error: "Invalid object name" / "Incorrect syntax near"
Hi All,This problem isent strictly to do with ASP.NET but I am creating an ASP.NET web application.Just a quick overview, I have recently had to reinstall windows on my machine becuase of an unrelated matter so before I did so i created an SQL script using the SQL Server Database Publishing Wizard so that the SQL Database could be recreated exaclty as it was.I have just reistalled SQL Server 2005 and the Managment Studio and have attempted to run the script.Once the script is run there are literally thousands of the following two errors:Invalid object nameandIncorrect syntax near ...It is ...

Error: "Failed to generate a user instance ...." SQL Server 2005 Express
Hi folks I have a web site that I am developing using VWDE 2008 that includes a SQL Server 2005 Express (SSE) on my HDD. My PC is a 64 bit Vista Ultimate. When I look at the Database Explorer pane, there is a red x next to the DB icon.  If I click on the expand symbol (+) a dialog box pops up with the message sqlserver.exe has stopped working.  When I close the window another error message pops up saying: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance.  The connection will be closed." The connec...

"Timeout expired" error occuring while fetching the data with SQL Server 2000
Hello,I have one application which is having written in asp.net & plain asp.I am having one button on asp page,when i will click on that button, then itwill execute one other asp page.And after the execution of that second asp page, I redirect it to someASPX page with some values.On the ASPX page, it will connect to the Database, and insert the values.Thus, sometime, the following error is occuring :"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."Main thing is that i am testing it on my local machine, then also ...

Formview gives Error converting data type nvarchar to numeric error when comma is in the input
I have a formview that collects various data including money and decimal data. I had a problem where the $ was casuing the "Error converting data type nvarchar to numeric" error and think I corrected it by changing the format to {0:C} for the field in the data bindings. I am still having an issue with commas in the data in a decimal field. The field in the database is decimal (14,9) and the format in the textbox in the formview is {0:n}. The data being input is something like 123,456.0. This gives the Error converting data type nvarchar to numeric error. If you remove the comma, 1...

Error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
I still don't know what is happening with my application. I used before in another page the formview control and I didn't have the out of range Date error. It was normally. But now, I'm doing everything manually and I don't know why, but I'm with this problem. I need to safe this problem today... Here go my code! The error occurs here: (It's the Sub that Insert data inside of the database(SQLServer)Protected Sub Gravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim SQL As String = "Update RECEBER SET  EMISSAO = '" & CType(tbDataEmissao.Text, Date) & "', VENCIME...

aspnet_Membership_GetPassword and aspnet_Membership_GetUserByName return "Error converting data type varchar to datetime"
I have an asp:PasswordRecovery control that stopped responding to "Forgot your password". I enter a valid user name (I know it's valid because I used it to login) and press next (this is normally where one gets their password mailed to them) and I get the error "Your attempt to retrieve your password was not successful, please try again". So I fired up SQL profiler and found that the code was calling first aspnet_Membership_GetUserByName() and then aspnet_Membership_GetPassword(). I tried the same calls in SQL Query and I got the error: "Erro...

Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message
Hi folks, I keep looking at these SQL text query and I cannot figure out what I am doing wrong.If I include wrap the dates with quotes, I get the "Syntax error converting datetime from character string" error message, but I don't include them I don't get anything which is also wrong, because there is some data to be displayed. Bottom line, what do you think I am doing wrong when building the SQL query?1 DateTime dateFrom = txtDateFrom.SelectedDate;2 DateTime from = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0);3 4 DateTime dateTo = txtDateTo.SelectedDate;5 DateTime to = new DateTime(dateTo.Year, dateTo.Month, dateTo.Day, 23, 59, 59);6 7 string template = lstTemplate.SelectedValue;8 9 String sql = @"SELECT BATCHES.BATCHID, BATCHDEFS.BATCHDEFNAME, BATCHES.REALPAGECNT, QUEUES.QUEUENAME, BATCHES.CREATIONTIME10 FROM BATCHES INNER JOIN BATCHDEFS11 ON BATCHES.BATCHDEFID = BATCHDEFS.BATCHDEFID12 INNER JOIN QUEUES13 ON BATCHES.QUEUEID = QUEUES.QUEUEID14 WHERE (BATCHES.CREATIONTIME)15 BETWEEN '@from' AND '@to' AND UPPER(BATCHDEFS.BATCHDEFNAME) = '@template' ORDER BY BATCHES.BATCHID;";16 17 SqlCommand command = ne...

How to solve:"The remote server returned an error: (500) Internal Server Error. "
 I am working On asp.net2.0(c#) In creating rss i used a user control by writing xml dynamically. It is working well in my personal server but in my client server it is producing error such that.. Server Error in '/GetReception' Application. The remote server returned an error: (500) Internal Server Error. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Net.WebException: The remote server returned an error: (500) Internal Server Error.Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [WebException: The remote server returned an error: (500) Internal Server Error.] System.Net.HttpWebRequest.GetResponse() +1529755 System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials) +61 System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials) +1865280 System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn) +51 System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver) +44 System.Threading.CompressedStack.runTryCode(Object userData) +54 System.Runtime.CompilerServices.RuntimeHelpers.Execute...

Web resources about - SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric" - perl.dbi.users

Resources last updated: 3/22/2016 11:30:18 PM