Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
accessdbguru
Starting Member
26 Posts |
 Posted - 2010-06-10 : 13:13:30
|
| Error: Divide by Zero encountered. How can I skip this error? contract earned and margin to date have both 0's in them.SQL Query:SELECT WS_Job_Number, Contract_Earned, [Margin To Date], CASE WHEN [Margin to Date] = 0 And [Contract_Earned] = 0 THEN '-' ELSE ([Margin To Date]/[Contract_Earned]) END AS '% Margin To Date' FROM dbo.vw_SMS_BackLog_2 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-10 : 14:18:31
|
| On way is to use a combination of the NULLIF() and COALESCE() functions. For example:SELECT COALESCE( 24.0 / NULLIF(0, 0), 0) AS Foo |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-10 : 14:27:01
|
| It's just the denominator you have to worry about, as 0 in the numerator will always give you 0.CASE WHEN [Contract_Earned] = 0 THEN 0 ELSE [Margin To Date]/[Contract_Earned])should do the trick. You can't make one part of your case statement a string('-') and the other a numeric, they both have to have compatible data types. You could also convert the else part to varchar, but do it like above and format later.JimEveryday I learn something that somebody else already knew |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-10 : 14:42:18
|
| [code]SELECT [Margin To Date]/NULLIF([Contract_Earned],0)[/code]CODO ERGO SUM |
|
|
|
accessdbguru
Starting Member
26 Posts |
Posted - 2010-06-10 : 16:10:34
|
| Thanks, But I need to differentiate the values with the undefinite error with a dash (-). How will I do that. thanks, SMS |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-10 : 16:27:05
|
| As stated before, you can only have one datatype for a column.If you need to display a - in a report, you should do that in the reporting tool, not in SQL.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Error: Divide by Zero encountered