Jump to content

Complex WHERE statement


Recommended Posts

New to w3schools. Great site! I've been throught the SQL tutorial and reference but still have a question for you SQL rockstarts out there :) In the following statement from a user defined function on MS SQL 2000:where DateWorked >= @DateStart and DateWorked <= @DateEnd and Department='50' and (Flag & 4)=0DateWorked, Department, and Flag are fields in the database. What does the "& 4" do in "(Flag & 4)=0?Thanks,Bear

Link to post
Share on other sites

this has something to do with determining packet size - so it is used (I think) to figure out how to handle long characters . . .hopefully someone can expand on this - thats all I can offer.

Link to post
Share on other sites

Thanks for the reply. "Flag" is defined in the database as a tinyint with a size of 1.The problem I'm having is that the "and (Flag & 4)=0" clause is allowing the number 2 in the results. The data for "Flag" is 0, 1,2 or 4 and the results returned from this where clause contain 0 & 2. :) Thanks!Bear

Link to post
Share on other sites

Bear,No, (Flag & 4) is a way of saying (Flag AND 4) but at a bit level. Here is an example inspired from this URL : http://en.wikipedia.org/wiki/Bitwise_operation.Imagine that the value that represent "Flag" is equal to the number 2, in binary, you write it like this : 010. The number 4 from your query is written like this : 100. Now you make your logical AND operation :010100 AND----000If the two bytes are equal to 1 then the result is 1, else it is 0. Once you have your result (000), you translate it to his decimal representation, in our case it's the number 0.Another example with Flag = 4100100 AND----100The result is 100 --> the number 4.Hope this help.

Link to post
Share on other sites

So...If Flag is 2 then (Flag & 4)= 0If Flag is 4 then (Flag & 4)=1 In fact the only time it will = 1 is when Flag=4.So in the expresion ... AND (Flag & 4) = 0 would return the same records as ... AND (Flag <> 4) because the selection criteria is "and records where the field 'Flag' is not 4".In testing this theory I did find it interesting that the records returned were the same but they were returned in a different order.Thanks for your help Jerome!!!! :)

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...