Author |
Topic |
jrobin747
Starting Member
48 Posts |
Posted - 2013-07-05 : 12:19:49
|
I'm new to SQLI'm SQL Server Manager 2008I have to write a query that gets a list of all the records from DataLinkMerhcants that have an ApplicationID <>0 where Assignedto is NULL or is 0I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the queryThis is what typedSELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedToFROM dbo.DatalinkMerchantWHERE ApplicationID <>0 AND AssignedTo=NULL OR 0 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-05 : 12:24:52
|
[CODE]SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedToFROM dbo.DatalinkMerchantWHERE ApplicationID <>0 AND (AssignedTo IS NULL OR AssignedTo = 0)[/CODE]quote: Originally posted by jrobin747 I'm new to SQLI'm SQL Server Manager 2008I have to write a query that gets a list of all the records from DataLinkMerhcants that have an ApplicationID <>0 where Assignedto is NULL or is 0I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the queryThis is what typedSELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedToFROM dbo.DatalinkMerchantWHERE ApplicationID <>0 AND AssignedTo=NULL OR 0
|
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-07-05 : 12:49:51
|
So I understand, why did you use parenthesis? Did you have to? Could it have been done another way. This is new to me.(AssignedTo IS NULL OR AssignedTo = 0) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 13:01:38
|
This has to do with the operator precedence. AND Has a higher precedence than OR - see here: http://msdn.microsoft.com/en-us/library/ms190276.aspx What that means is that if you don't have paranthesis, SQL Server will first evluate the "ApplicationID <>0 AND AssignedTo=NULL" . Then it will use the result of that operation an OR it with 0. Using the paranthesis forces the OR operation to be evaluated first.So whether you need to use paranthesis or not depends on the logic you are trying to implement. |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-05 : 14:44:08
|
Just to add to James K comments. The () puts thing in order on how it is evaluated. For example. Think of Algebra.4 * (9 + 3) = 484 * 9 + 3 = 39In Algebra, the equation in () are done first thus the two different answers shown above. This is how I visualize in determining if and when I need to use (). |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 15:01:14
|
quote: Originally posted by ugh3012 Just to add to James K comments. The () puts thing in order on how it is evaluated. For example. Think of Algebra.4 * (9 + 3) = 484 * 9 + 3 = 39In Algebra, the equation in () are done first thus the two different answers shown above. This is how I visualize in determining if and when I need to use ().
That is a very good explanation! That is what I should have said instead of talking about operator precedence and such crap |
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-07-05 : 15:07:16
|
Thanks so much.From a math perspective it makes all the sense in the word. As newbie programmer, based on my assignment to write the query I would have no idea what order should be taken. I ran the query with and without quotes and got the same answer 83 records.I guess the other hard thing in learning this stuff is somethimes even if I received the message "Query executed successfully" I wouldn't know if I ACTUALLY got the proper results or not.Yes in the temporary table ApplicationID records have a number <>0 and the AssignedTo records either have 0 or NULL. So I'm guessing all is well.I'm told it will make sense eventually. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 16:06:03
|
quote: Originally posted by jrobin747 Thanks so much.From a math perspective it makes all the sense in the word. As newbie programmer, based on my assignment to write the query I would have no idea what order should be taken. I ran the query with and without quotes and got the same answer 83 records.I guess the other hard thing in learning this stuff is somethimes even if I received the message "Query executed successfully" I wouldn't know if I ACTUALLY got the proper results or not.Yes in the temporary table ApplicationID records have a number <>0 and the AssignedTo records either have 0 or NULL. So I'm guessing all is well.I'm told it will make sense eventually.
Did you mean with and without the brackets? That doesn't sound quite right, unless the data happened to be such, which doesn't seem very likely - but you never know.The way to debug it is to look at a subset of the data that you can manually examine and compare with what the query returns. So, for example you might add another where clause:WHERE ApplicationID<>0 AND (AssignedTo IS NULL OR AssignedTo = 0)AND ApplicationID = 12345 Now look at all the rows from the table that have ApplicationId = 12345 and manually examine each row and compare with the results from the query. If AssignedTo is not null and it is not zero, then that row should not be there, if it is NULL it should there etc. |
|
|
|