|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Explain this please!I am a beginner in this SQL Programming and trying to learn everything as quick as possible. I came across a query below and have a very hard time in understanding it. Specially the WHERE part of the clause. I do not see any value "0x400" in the column Flags but it gives a result. SELECT SUM(balance) AS bal, accounts FROM (SELECT SUM(Amount1) AS balance, Code AS accounts FROM dbo.data WHERE (Flags & 0x400 = 0x400) GROUP BY Code UNION SELECT SUM(Amount2) AS bal, BookNo AS accounts FROM dbo.data WHERE (Flags & 0x400 = 0x400) GROUP BY BookNo) AS a GROUP BY accounts Lookup the (BITWISE AND) operator in BOL.
Show quote "MackS" <nom***@holmanhouse.com> wrote in message news:uRxzLsx1GHA.1548@TK2MSFTNGP02.phx.gbl... > Hello, > > I am a beginner in this SQL Programming and trying to learn everything as > quick as possible. > > I came across a query below and have a very hard time in understanding it. > Specially the WHERE part of the clause. > I do not see any value "0x400" in the column Flags but it gives a result. > > > SELECT SUM(balance) AS bal, accounts > FROM (SELECT SUM(Amount1) AS balance, Code AS accounts > FROM dbo.data > WHERE (Flags & 0x400 = 0x400) > GROUP BY Code > UNION > SELECT SUM(Amount2) AS bal, BookNo AS accounts > FROM dbo.data > WHERE (Flags & 0x400 = 0x400) > GROUP BY BookNo) AS a > GROUP BY accounts > ok, thanks.
but, i could not find anything on (Flags & 0x400 = 0x400) Specially the 0x400 = 0x400, is there any special meaning for using this number? On which occasions we use it? Show quote >> WHERE (Flags & 0x400 = 0x400) Have you looked in all the right places?
http://msdn2.microsoft.com/en-us/library/ms174965.aspx ML --- http://milambda.blogspot.com/ It is used to check if a bit in the int (as it often is) is turned on or not. Consider below, read
from right to left: etc. 2048 1024 512 256 128 64 32 16 8 4 2 1 0x400 is 1024 in decimal notation. So this is used to see if the 11:th bit (right to left) it turned on in the int. The value for the int might be, for instance (in decimal notation) 1025, which means that the first and 11:th bit is turned on.... This is how many of the status column in the old-style system tables (2000 and earlier) are designed, see for instance sysdatabases.status. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "MackS" <ma***@yahooa.com> wrote in message news:egDR$7B2GHA.4108@TK2MSFTNGP04.phx.gbl... > ok, thanks. > but, i could not find anything on (Flags & 0x400 = 0x400) > Specially the 0x400 = 0x400, is there any special meaning for using this number? > On which occasions we use it? > >>> WHERE (Flags & 0x400 = 0x400) > > Dear ML and Tibor Karaszi
Thank you for the reply. I reading those pages now and trying to understand the things. Sorry for asking the same question again. In this particular query's where part is WHERE (Flags & 0x400 = 0x400) I understand by reading the texts the "Flags & 0x400" is a bitwise ANDing. But, what I do not understand is why there is a "= 0x400". Thank you again Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:ucsOWDC2GHA.3464@TK2MSFTNGP03.phx.gbl... > It is used to check if a bit in the int (as it often is) is turned on or not. Consider below, read > from right to left: > > etc. 2048 1024 512 256 128 64 32 16 8 4 2 1 > > 0x400 is 1024 in decimal notation. So this is used to see if the 11:th bit (right to left) it turned > on in the int. The value for the int might be, for instance (in decimal notation) 1025, which means > that the first and 11:th bit is turned on.... This is how many of the status column in the old-style > system tables (2000 and earlier) are designed, see for instance sysdatabases.status. > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "MackS" <ma***@yahooa.com> wrote in message news:egDR$7B2GHA.4108@TK2MSFTNGP04.phx.gbl... >> ok, thanks. >> but, i could not find anything on (Flags & 0x400 = 0x400) >> Specially the 0x400 = 0x400, is there any special meaning for using this number? >> On which occasions we use it? >> >>>> WHERE (Flags & 0x400 = 0x400) >> >> > Further to this problem
When I execute the following query select flags from data_table where Flags & 0x400 = 0x400 group by flags I can the vlues 1024 and above and when I execute like this select flags from data_table where Flags | 0x400 = 0x400 group by flags I get the values 1024 and below. All values corresponds to the numbers in "power 2" series in Tibor Karaszi example Thanks guys! I feel that I ma beginning to understand more. "MackS" <ma***@yahooa.com> wrote in message news:eGnHtJC2GHA.5048@TK2MSFTNGP05.phx.gbl... Dear ML and Tibor KarasziThank you for the reply. I reading those pages now and trying to understand the things. Sorry for asking the same question again. In this particular query's where part is WHERE (Flags & 0x400 = 0x400) I understand by reading the texts the "Flags & 0x400" is a bitwise ANDing. But, what I do not understand is why there is a "= 0x400". Thank you again Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:ucsOWDC2GHA.3464@TK2MSFTNGP03.phx.gbl... > It is used to check if a bit in the int (as it often is) is turned on or not. Consider below, read > from right to left: > > etc. 2048 1024 512 256 128 64 32 16 8 4 2 1 > > 0x400 is 1024 in decimal notation. So this is used to see if the 11:th bit (right to left) it turned > on in the int. The value for the int might be, for instance (in decimal notation) 1025, which means > that the first and 11:th bit is turned on.... This is how many of the status column in the old-style > system tables (2000 and earlier) are designed, see for instance sysdatabases.status. > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "MackS" <ma***@yahooa.com> wrote in message news:egDR$7B2GHA.4108@TK2MSFTNGP04.phx.gbl... >> ok, thanks. >> but, i could not find anything on (Flags & 0x400 = 0x400) >> Specially the 0x400 = 0x400, is there any special meaning for using this number? >> On which occasions we use it? >> >>>> WHERE (Flags & 0x400 = 0x400) >> >> > You cannot just way "WHERE 1" or similar in TSQL. You need some predicate, like =, BETWEEN, >, IN
etc. Say the a row has in the Flags column (binary): 100101 An you check for (&) 0x2, binary: 000010 "Bitwise and" will return 1 where both are 1, i.e. 000000 So, you now, on the left hand side have 000000, and you compare it to 000010, which is false. Say you have another row, with the value 001011, again bitwise and with 000010, which returns 000010. Compare 000010 to 000010 which is true. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "MackS" <ma***@yahooa.com> wrote in message news:eGnHtJC2GHA.5048@TK2MSFTNGP05.phx.gbl... Dear ML and Tibor KarasziThank you for the reply. I reading those pages now and trying to understand the things. Sorry for asking the same question again. In this particular query's where part is WHERE (Flags & 0x400 = 0x400) I understand by reading the texts the "Flags & 0x400" is a bitwise ANDing. But, what I do not understand is why there is a "= 0x400". Thank you again Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:ucsOWDC2GHA.3464@TK2MSFTNGP03.phx.gbl... > It is used to check if a bit in the int (as it often is) is turned on or not. Consider below, read > from right to left: > > etc. 2048 1024 512 256 128 64 32 16 8 4 2 1 > > 0x400 is 1024 in decimal notation. So this is used to see if the 11:th bit (right to left) it > turned > on in the int. The value for the int might be, for instance (in decimal notation) 1025, which > means > that the first and 11:th bit is turned on.... This is how many of the status column in the > old-style > system tables (2000 and earlier) are designed, see for instance sysdatabases.status. > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "MackS" <ma***@yahooa.com> wrote in message news:egDR$7B2GHA.4108@TK2MSFTNGP04.phx.gbl... >> ok, thanks. >> but, i could not find anything on (Flags & 0x400 = 0x400) >> Specially the 0x400 = 0x400, is there any special meaning for using this number? >> On which occasions we use it? >> >>>> WHERE (Flags & 0x400 = 0x400) >> >> >  |
|||||||||||||||||||||||
Other interesting topics