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 |
|
grizdog
Starting Member
4 Posts |
 Posted - 2012-02-24 : 15:34:53
|
I have the following bit of code inside a where clause in my query:(other.CreditsEarned > 0 and other.GradePoints/other.CreditsEarned > 1.5) or other.CourseID ='102054' So I want to include every for credit course that has a certain minimum grade, and if the course with ID 102054 is there, I want that too. Course 102054 is a no-credit course, so other.CreditsEarned will always equal 0 for that course. The query runs fine if I take out the "or other.CourseID = '102054'" part, but when I put it in, I get a divide by zero error.I looked at the sql-helper pages, but they seem to have gone away. There is something non-procedural going on here - I'd like to know how to fix my code, but also how does the where clause do its selectio - if this were an fi statement in C, just putting the divide by zero test first would e enough - here, it is not.Thanks. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
grizdog
Starting Member
4 Posts |
Posted - 2012-02-24 : 20:45:56
|
| Thanks, that worked - and it gives me a procedural structure to work with.Thanks again. |
 |
|
|
grizdog
Starting Member
4 Posts |
Posted - 2012-05-07 : 10:09:59
|
| OK, that was great, but now I have a followup question. In most procedural languages, like C, a line of code like this will work:if x != 0 then z = y/x else z = 0Point being, it will check to see if x=0, and if it is, then it jumps straight to z=0 and never tries to compute y/x, thereby avoiding a divide by zero exception. Near as I can tell from some tests, SQL does not do this - my if-then clauses always seemed to fail, but a case statement like that given above worked. Seems strange? Can anyone tell me if this is how it is supposed to work, and why?Thanks. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-07 : 10:41:14
|
| It should work as you described and should not cause a divide by zero. Can you post and example where it fails? Unlike procedure languages, where IF and CASE (or SWITCH) can be substituted for one another (with some effort perhaps), in T-SQL they are used in different contexts. Some of the scripting languages, for example those that you might use in SSRS expressions evaluate both sides and choke if either causes division by zero. But I assume you are talking about T-SQL. |
|
|
|
grizdog
Starting Member
4 Posts |
Posted - 2012-05-08 : 12:11:27
|
| OK, the original code was part of a large query that I don't want to reproduce here, and trying it in small snippits I have not been able to recreate it. So it looks like it works correctly. I haven't been using scripting languages, but I do dabble in other flavors of SQL - I imagine they all take the same, procedural approach so you can avoid division by zero by putting the test ahead of the division in the same statement?Thanks. |
|
|
|
|
|
|
|
|
Divide by zero