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 |
|
yotafan
Starting Member
1 Post |
 Posted - 2007-02-23 : 15:48:47
|
| Hello all, i hope someone that understands SQL can help me.I have a report that runs from a access query, which is simple.My problem is that i get an overflow error becuase one of the queries formulas are to divide some numbers and in this one instance there is a "0" involved.My formula is:Col2%: [Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])The problem is that [Col 2 Hrs]=0 this month for a specific employee.In excel i know i can do=IF([Col 2 Hrs]=0,"0",([Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])))and that works fine.Can someone please show me how to do this in SQL so instead of returning an error it will display 0 or N/A.Thanks inadvanceHope i explained my issed well enough for everyone. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-02-23 : 15:53:58
|
| I don't think your issue is when [Col 2 Hrs] is 0, since that evaluates to zero with no error. I think your issue is when ([TotHrs]-[Col 6 Hrs]) = 0 , since that will throw an error. It is very important to be familiar with basic algebra when working with computer programming, so be sure that these concepts make sense. When the numerator is 0 you are fine, it is when the denominator is zero that you will get an error.Anyway, I am not sure if you are using Access or SQL here, but Access has an IIF() function that works exactly like IF() in Excel, and SQL Server has a CASE expression that works basically the same way.Access 2003:=IIF([TotHrs]-[Col 6 Hrs] = 0, 0, [Col 2 Hrs] / ([TotHrs]-[Col 6 Hrs])))SQL:case when [TotHrs]-[Col 6 Hrs] = 0 then 0 else [Col 2 Hrs] / ([TotHrs]-[Col 6 Hrs]) end- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
MS Access 2003 SQL View