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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Basic Query Help

Author  Topic 

jrobin747
Starting Member

48 Posts

Posted - 2013-07-05 : 12:19:49
I'm new to SQL
I'm SQL Server Manager 2008
I 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 0

I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the query

This is what typed
SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedTo
FROM dbo.DatalinkMerchant
WHERE 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.AssignedTo
FROM dbo.DatalinkMerchant
WHERE ApplicationID <>0 AND (AssignedTo IS NULL OR
AssignedTo = 0)
[/CODE]

quote:
Originally posted by jrobin747

I'm new to SQL
I'm SQL Server Manager 2008
I 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 0

I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the query

This is what typed
SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedTo
FROM dbo.DatalinkMerchant
WHERE ApplicationID <>0 AND AssignedTo=NULL OR 0

Go to Top of Page

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)

Go to Top of Page

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.
Go to Top of Page

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) = 48
4 * 9 + 3 = 39

In 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 ().
Go to Top of Page

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) = 48
4 * 9 + 3 = 39

In 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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -