Divide By Zero Exception
Jun 10, 2006How I can rectify divide by zero exception in stored procedure
View 12 Replies
ADVERTISEMENT
'((System.Exception)($exception)).Message' Threw An Exception Of Type 'System.NotSupportedException'
Jan 16, 2008Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic.
'((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer)
http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form.
http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
private void lnkFavoriteAccount_Click(object sender, EventArgs e)
{
FinancesDataSet.BankAccountRow account = this.financesDataSet.BankAccount.NewBankAccountRow();
account.Name = "MyBank Checking Account";
account.AccountType = "Checking";
account.Balance = Convert.ToDecimal("15.03");
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account);
//The next three lines where added while I was trying to get this to work.
//I don't know if I really need them or not, I receive the error regardless if these are here or not.
this.bankAccountTableAdapter1.Update(financesDataSet);
this.financesDataSet.AcceptChanges();
refreshDatabase();
}
the refreshDatabase() code is here:
private void refreshDatabase()
{
this.bankAccountTableAdapter1.Fill(this.financesDataSet.BankAccount);
//Aquire a count of accounts the user has
int numAccounts = financesDataSet.BankAccount.Count;
//Loop through each account and see which one is the primary.
for (int num = 0; num != numAccounts; num++)
{
//Works ok in frmMain_Load, but when my lnkFavoriteAccount_click calls this, it throws the error.
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
{
//Display the primary account on our home page. User can click the link label & be taken to their account register.
this.lnkFavoriteAccount.Text = this.financesDataSet.BankAccount[num].Name.ToString();
this.lnkFavoriteFunds.Text = this.financesDataSet.BankAccount[num].Balance.ToString();
break;
}
}
}
and my form_load code
private void frmMain_Load(object sender, EventArgs e)
{
refreshDatabase();
}
So, when I click on the lnkFavoriteAccount label, and my new row gets added, the app stops at the following line in my DataSet.Designer
[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]
public byte FavoriteAccount {
get {
try {
return ((byte)(this[this.tableBankAccount.FavoriteAccountColumn]));
}
catch (global:ystem.InvalidCastException e) {
//Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
}
}
set {
this[this.tableBankAccount.FavoriteAccountColumn] = value;
}
}
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
Thanks for any help you guys can offer.
Johnathon
The Script Threw An Exception: Exception Of Type 'System.OutOfMemoryException' Was Thrown.
Jan 31, 2007Hi,
I got an strange problem with one of my packages.
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Regards,
Jan
Divide By Zero
Jul 27, 2001I have a field that divides one field by the sum of two others. However, when both of the two latter fields are 0's, I get a divide by zero error and the output halts. How do I get this so that for these records I put a NULL or UNDEFINED or something in there at least so I can see the rest of the output. Thanks.
View 1 Replies View RelatedHow To Get Around Divide By Zero
May 1, 2008I have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please
SELECT
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,
SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / (SUM(safe) + SUM(unsafe)))AS [% Safe],
SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks,
dbo.Employee.emp_user_id
FROM
dbo.safety_obs_data
INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no
WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102))
GROUP BY dbo.Employee.emp_user_id
Divide By Zero
Jun 5, 2008SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END))
= 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)) END AS Rspec_Vulgar
FROM dbo.TekliveQuery
---
Note:
How can I avoid the the error message if the dividend and the divisor are both 0?
Divide By Zero
Mar 8, 2006When I specify a formula between Computed Column Specification, I have twozero values, getting Divide by Zero error, any idea how can I avoid this? Istill want SQL Server to display Zero if it is 0/0, is this possible in SQLServer database?ThanksJ.
View 3 Replies View RelatedDivide By Zero
Feb 12, 2007Hi,
In the BI developement, in a matrix i'm trying to avoid division by zero, but even if i test the field before the division, i've got the error everytime.
I put the folowing test condition in the field, but i've got an error.
Ex : iif(Turnover <> 0, Cost/Turnover,0)
But i've got error message when the turnover is 0.
Thanks for your help.
Sql Preventing Divide By Zero
Jul 4, 2007 1 ALTER PROCEDURE links_GetAllLinks
2 @startRowIndex INT,
3 @maximumRows INT,
4 @sortExpression nvarchar(50)
5 AS
6
7
8
9
10
11 SET @startRowIndex = @startRowIndex + 1
12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END
13 -- populate the table CAST(getdate() as int)
14
15
16 IF @sortExpression LIKE ''
17 BEGIN
18 SET @sortExpression = 'links_Links.link_date'
19 END
20 DECLARE @sql nvarchar(4000)
21 SET @sql = 'Declare @Links TABLE
22 (RowNumber INT,
23 link_id INT,
24 cat_id INT,
25 cat_title VARCHAR(100),
26 sub_id INT,
27 sub_name VARCHAR(100),
28 link_aproved BIT,
29 link_name VARCHAR(100),
30 link_url varchar(100),
31 link_desc varchar(500),
32 link_date datetime,
33 link_rating INT,
34 link_ratingscount INT,
35 link_ratingvalue INT,
36 link_poster uniqueidentifier,
37 link_comments INT,
38 hit_date datetime);INSERT INTO @Links
39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date
40 FROM links_Links INNER JOIN
41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN
42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id
43 WHERE links_Links.link_aproved = 1;SELECT * from @Links
44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1'
45
46
47
48 EXEC sp_executesql @sql
49
50
51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
SQL Divide By Zero Problem
Mar 31, 2006Hi all,
I am having a terrible time with a stored procedure. I think it has something to do with a divide by zero issue. Here is my sproc:
ALTER PROCEDURE [dbo].[GetPlayers]
@TeamID int
AS
SELECT PlayerID,
TeamID,
FirstName,
LastName,
FirstName + ' ' + LastName As Name,
"SPct" =
CASE (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID)
WHEN 0 THEN 0
ELSE
Cast
( Cast
( (
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '1') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '2') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '3') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '4')
/
(SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID
) * 1.0
) As int
) As Decimal(4,3))
End
FROM Player
WHERE TeamID = @TeamID
ORDER BY FirstName, LastName
Here is the error I get when I run this:
Msg 8115, Level 16, State 8, Procedure GetPlayers, Line 9
Arithmetic overflow error converting int to data type numeric.
So the results should look like: .333 or .500 or .000
It's driving me crazy. I have tried everything but cannot get the desired results. I'm hoping someone can help me.
Thanks,
Bob
Supressing Divide By Zero
Oct 19, 2004I know you can resolve "divide by zero" messages with a CASE statement.
However, the T-SQL docs suggest that I should be able to simply turn off both the error message and the effect of the error with
SET ARITHIGNORE ON
and
SET ARITHABORT OFF
I am trying to execute these two statements within a stored proc but I need their effect to reach to a select statment that is a sql string (as in 'EXECUTE (@SQLSTRING)' within the same stored proc.
Am I wrong to expect these statements to be able to deal with "divide by zeroes"?
I haven't been able to make it work so I would appreciate any pointers that someone might have.
Many thanks.
How To Set Decimal After Divide
Nov 23, 2006let say
1/2 = 0.5
anyone know how to set it become 0.5? by default the value will become 0,
SQL Query Help. DIVIDE
Dec 5, 2007this is from a SQL playbook
i don't understand the logic of DIVIDE when you use more than one NOT EXISTS
such as this query
select distinct itemname from qdel del where not exists (select * from qspl
where not exists (select * from qitem where itemtype='n' and not exists
(select * from qdel where qdel.itemname=qitem.itemname
and qdel.splno=qspl.splno)) and not exists (select * from qdel
where qdel.itemname = del.itemname and qdel.splno=qspl.splno))
i don't understand how can NOT EXISTS code brings out the results of the existing rows in those not exists lines
like it says NOT EXISTS itemtype 'n' etc. but that is the results!
PLEASE HELP - Divide Statement
May 7, 2008I would like to divide the value of column_2 by the value of column_3 and have the result returned in a new_column.....
"SELECT column_1, column_2, column_3, (dbo.column_2 "DIVIDED BY" dbo.column_3) AS new_column
FROM dbo"
Do I have to create a 'temp table' to do this?
If you cannot tell, I am VERY new to this and would appreciate any help!!!
Thank you in advance! Nathan
Divide By Zero Encountered
Jun 4, 2008I'm getting such error. I want the result as blank once it's divided by zero. Is that possible?
Here's my formula:
SELECT (SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END) * 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0)
/ (SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END)
* 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END)) * 1.0 AS Info_Other
FROM dbo.TPhones_Oct
Divide By Zero Error
Feb 24, 2006Hello,
When I execute the following query, I get a Divide by zero error. I believe the Divide By zero error is occuring in the following statement. How do I avoid the error?
ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
drop table #MaxArchiveDates
DECLARE @MaxArchiveDate INTEGER
, @EdgeModelID INTEGER
, @DefinitionID INTEGER
SELECT ArchiveDataDate = MAX(ArchiveDataDate) INTO #MaxArchiveDates FROM intldbArchive.dbo.GOModelUniversesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecurityGroupModelValSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecuritiesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOUniverseSecurityMembeSaveD
SELECT @MaxArchiveDate = MIN(ArchiveDataDate) FROM #MaxArchiveDates
SELECT @MaxArchiveDate
SELECT ModelID = today.ModelID
, RelativeUniverseID = today.RelativeUniverseID
, SecurityID = today.SecurityID
, ModelValueT = CONVERT(decimal (8, 4), today.ModelValue)
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
FROM OptMod.dbo.GO_SecurityGroupModelValues today
JOIN intldbArchive.dbo.GOSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = @MaxArchiveDate
Thanks in advance!!!
sqlnovice123
Divide By Zero Error
Feb 28, 2006I am writing a report that deals with time in seconds. I am trying to figure out the average handle time for each call.
To Get this number i use 5 fields. the formula is
(((cs_acd_trk_t + cs_acd_ext_c)/(cs_acd_trk_c + cs_acd_ext_c)) + ((cs_t_acd_wrk)/(cs_acd_trk_c + cs_acd_ext_c))/(cs_acd_trk_c + cs_acd_ext_c))
Which translates to ((Incoming Handle Time) + (Work))/(Number of Incoming Calls)
When it does the calculations some times the cs_acd_trk_c + cs_acd_ext_c (number of incoming calls) is zero.
I dont know how to get around this ... i tried to set that value to <>0 in my query but it threw my other numbers off for number of calls which is a different value on the report.
Any Help on this would be super appreciated.
Thanks,
Mitch
Divide By Zero Problem
Oct 16, 2007I have a query that goes against an Oracle 9i database. I have run it in RS and in native oracle environment and get the same query results. I have guaranteed there are no NULL values.
If I structure the expression (as a 0 value test) it works fine. It puts 0/1 for in the result field correctly.
=iif(Fields!LDLM.Value = 0, 0.0, 1.0)
If when I put in the actual divide statement I get a zero divide error.
=iif(Fields!LDLM.Value = 0, 0.0,
((Fields!LDTM.Value - Fields!LDLM.Value) / Fields!LDLM.Value) * 100)
What could possibly be wrong? What is it I do not see?
This is as easy as it gets but it's driving me crazy !!!!!
Any thoughts. ANYBODY !!!!
Divide By Infinity
Apr 2, 2007help... I have a field called PercentVar_P4 which really is budget - actuals... I need to create an expression that will that the
PercentVar_P4/budget.. when I do I get Infinity and nan... I want to see the negative number.. I have read through all the blogs and nothing seems to fit..
Please HELP!!
Divide By Zero Error Only With Top
Sep 27, 2006In the following expression, the query executes with no errors (there are sometimes 0's and nulls in IRON and ENERGY):
SELECT * FROM gff2vg
WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)
order by IRON/ENERGY desc
but when TOP is added there is a divide by zero error. Is there a way to avoid this?
SELECT TOP(64) * FROM gff2vg
WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)
order by IRON/ENERGY desc
Thanks.
Attempted To Divide By Zero
Sep 26, 2005Hello.
View 7 Replies View RelatedAttempted To Divide By Zero
Jan 4, 2006hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
Help With Divide By 0 Function
May 2, 2008I'm new at SSRS and writing functions. I don't have something right here, but can't see what it is.
My function is:
Public Function TestDivide(Numerator as double, Divisor as double)
dim retVal
if Numerator <= 0 then
retVal = 0
end if
if Divisor= 0 then
retVal =0
else
retval = Numerator/Divisor
end if
return retVal
End Function
I have to test both the numerator and divisor because either or both may be 0.
In the cell I call it with this formula, thinking that the black part is the first argument and the red part is the second. But SSRS tells me "Argument not specified for parameter "Divisor".
=Code.TestDivide(RunningValue(Fields!Sales.Value,sum,nothing) - RunningValue(Fields!Cost.Value,sum,nothing)) ,
RunningValue(Fields!Sales.Value,sum,nothing)
Can someone tell me what I'm doing wrong? Thanks!
#Error Using IIF And Divide By Zero
Jan 19, 2007I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:
= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)
I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:
IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)
I guess the 42 is cast to an INT inside the second IIF and the calculation works.
What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.
Has anybody run into this problem? Is my workaround the recommended approach?
-Larry
Divide By Zero Error SQL SERVER
Jul 31, 2006Hi
Is there a way to prevent this error?
I'm getting it from query line:
10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)),1) 'Atingimento Semestre'
Thanks a lot
OLAP - Divide By Zero Error??
Sep 5, 2001Some of my calculated members look like "######" in Excel and I believe it is because of divide by zero errors. I have tried right clicking on the table, selecting "Table Options" and "For error, values, show:" I have enter 0. This is not working.
Does anybody know how to get rid of this.
Thanks,
Ruaidhri.
How To Divide A Field Into More Fields?
Apr 7, 2008Hello,
I have a field like below which has blanks 'b';
CODE
000123'b'99999'b'RED RING
WHAT i want to do is like below?
FIELD1
000123
FIELD2
99999
FIELD3
RED RING
Divide By Zero Error Encountered
Oct 31, 2013I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.
I am calculating Average Number of patients per day per nurse by using below query
SELECT Nurse,
COUNT(DISTINCT patientName) AS NoOfPatients,
COUNT(DISTINCT patientName) * 1.0/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]
FROM Table t
GROUP BY NURSE
but if ArrivalDate is Null I am getting below error saying that Divide by zero error encountered.
How To Avoid Divide By Zero Error
Nov 6, 2013I have some SQL scripts for SQL Views and getting divide by zero error. I am using
COALESCE(Field1,Field2,1)/COALESCE(Field3,Field4,1)
This function can avoid NULL but how I can avoid 0 ?
Is there any function in SQL that can take care both NULL and 0 and replace it with 1.
If Statement To Correct Divide By Zero
May 2, 2007I have this statement, sometimes the Dominator can be zero so i tried this if statement but the error i'm getting is:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(42000,147)
This is the query i'm using:
SELECT DBO.THIT_RATIO_DETL.F_DIVISION_NO,
Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END) AS BOUND,
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE,
Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END)*1.0 / If(Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)= 0 Then 1 else Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)) as HIT_RATIO
FROM dbo.THIT_RATIO_DETL
Group by dbo.thit_ratio_detl.F_DIVISION_NO
Not sure if in SQL server i can correct the problem like that, any help is greatly appriciated...
Divide By Zero Error Encountered.
Dec 3, 2007How can i handle this kind of error in my execution of my storedprocedure? Error:Divide by zero error encountered.
thanks!
Funnyfrog
Divide By Zero Error Trapping
Jul 20, 2005I have the following line in a select statement which comes up with adivide by zero error.CAST(CASE Splinter_StatusWHEN 'SUR' THEN 0ELSE CASE WHEN Sacrifice>=1THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/(m.Gross+m.Sacrifice)ELSE 0ENDEND AS Float)AS Bond2,The error happens on the section (m.Gross + m.Sacrifice) as this canequal zero and throws out the part of the calc that divides by it. Itis correct in some instances that it does so. The full SQL statementhas a large number of these expressions so I need a method I can applyto any line if possible.I know that it is mathmatically correct to error where this value iszero, but what I want to do is set the output of the entire expressionto zero if there is an error.Realistically an error such as this could happen at a few points inthe expression (or one of many others), so I need to find a way ofcatching any error in the expression and setting the return value to0. I thought of using a CASE statement, but wondered if there was abetter way of looking at this as the case statement would have tocheck each variation where it could throw an error.Any ideas ?ThanksRyan
View 3 Replies View RelatedWht Does This Give Divide By 0 Error?
May 24, 2007I am using iif which works just fine in Access to prevent divide by zero errors but this:
=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")
Fails
As Does:
=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)
Why? How do I get around this?