Complex WHERE statement

[Bear]

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

[Skemcin]

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.

[Bear]

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

[Jerome]

The '&' caracter allows a bitwise logical AND operation between two integer values. That means that the expression "Flag & 4" permits to compare two numbers at bit nivel. Here is a complete explanation.

[Bear]

So (Flag & 4) = 0 is a way of saying Flag <> 4 at a bit level?

[Jerome]

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.

[Bear]

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!!!!