Help With Divide By 0 Function
May 2, 2008
I'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!
View 1 Replies
ADVERTISEMENT
Aug 15, 2006
I am trying to divide 2 funcitons by each other and I am coming back with 0 as my result in one of them while I am getting the correct answer in another. The two columns I am trying to pull with functions are:
SELECT
sum(D.PrincipalBal)/sum(L.PrincipalBal)*100 as DelqRatioByBal
, count(D.LoanID)/count(L.LoanID)*100 as DelqRatioByCnt
FROM Loan L
INNER JOIN DelqINFO D on D.LoanID = L.LoanID
I get the correct result back for 'DelqRatioByBal' but I get 0 back for 'DelqRatioByCnt'
Any suggestons?
View 3 Replies
View Related
Jun 9, 2015
When I am doing the divide all values are showing Zero.
DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample
and also I want to show the QTR with %, for example, 66.57%
View 6 Replies
View Related
Oct 9, 2015
I have a work database where I implemented a table-valued function. One colleague of mine reported to me that this function gave a Divide by Zero error when executed with some specific values given to its arguments (there are a 15 arguments). Then I started debugging, and I introduced some exit points to the function before its end in order to detect the point where the error appeared, since I don't have access to the database server and I cannot use the debugging tools from remote, due to the network configuration of my office. I can only do attempts on the code to try to find a solution.
Since I didn't manage to get rid of this error, I decided to make a silly and desperate attempt: I put a RETURN statement immediately after the BEGIN of the function body, with the idea that the function should not raie any error if it exit immediately after its beginning, despite the fact that this results in an empty table in return.
The result of my attempt is that the Divide by Zero error is still THERE (!), even if my function looks like
ALTER FUNCTION [dbo][<myFuncName>](...parameters...) RETURNS TABLE (...table definition...) AS BEGIN
RETURN
END
GO
How I can check it.
View 5 Replies
View Related
Jul 27, 2001
I 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 Related
May 1, 2008
I 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
View 8 Replies
View Related
Jun 5, 2008
SELECT 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?
View 5 Replies
View Related
Mar 8, 2006
When 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 Related
Feb 12, 2007
Hi,
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.
View 3 Replies
View Related
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?
View 4 Replies
View Related
Mar 31, 2006
Hi 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
View 3 Replies
View Related
Oct 19, 2004
I 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.
View 2 Replies
View Related
Nov 23, 2006
let say
1/2 = 0.5
anyone know how to set it become 0.5? by default the value will become 0,
View 3 Replies
View Related
Dec 5, 2007
this 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!
View 10 Replies
View Related
May 7, 2008
I 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
View 4 Replies
View Related
Jun 4, 2008
I'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
View 16 Replies
View Related
Feb 24, 2006
Hello,
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
View 1 Replies
View Related
Feb 28, 2006
I 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
View 2 Replies
View Related
Oct 16, 2007
I 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 !!!!
View 8 Replies
View Related
Apr 2, 2007
help... 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!!
View 1 Replies
View Related
Sep 27, 2006
In 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.
View 7 Replies
View Related
Jan 4, 2006
hi,
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!
View 12 Replies
View Related
Jun 10, 2006
How I can rectify divide by zero exception in stored procedure
View 12 Replies
View Related
Jan 19, 2007
I 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
View 14 Replies
View Related
Jul 31, 2006
Hi
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
View 2 Replies
View Related
Sep 5, 2001
Some 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.
View 1 Replies
View Related
Apr 7, 2008
Hello,
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
View 1 Replies
View Related
Oct 31, 2013
I 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.
View 5 Replies
View Related
Nov 6, 2013
I 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.
View 5 Replies
View Related
May 2, 2007
I 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...
View 8 Replies
View Related
Dec 3, 2007
How can i handle this kind of error in my execution of my storedprocedure? Error:Divide by zero error encountered.
thanks!
Funnyfrog
View 3 Replies
View Related
Jul 20, 2005
I 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 Related