SQL Question

Results 1 to 3 of 3

Thread: SQL Question

  1. #1
    Jed Farr Guest

    Default SQL Question

    I have a table with an INT field called BitField and I would like to get the Bitwise-OR of all of the BitField fields and retrieve them (ideally from a query). I am currently using a stored procedure that uses a WHILE loop and a cursor to step through the records. This isn&#039t as fast as I&#039d like. Any suggestions ?<BR><BR>Thanks,<BR><BR>Jed.

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: SQL Question

    Yeah, I&#039ve got a suggestion, assuming I am understanding your problem correctly. Correct me if I&#039m wrong, but there&#039s a column in the table with a bit field, and you want to do a bitwise OR on a subset of rows? If so, we can use a little logic to find a quick workaround. Since for bitwise OR&#039s, if just ONE of the bit fields is set to one, then the result will be one. So, let&#039s say we want to do a bitwise OR to the subset of rows returned by this query:<BR><BR>SELECT * FROM Table1 WHERE &#060;something><BR><BR>From these rows, we want to either get a 1 or 0: a 1 if ANY of the columns has its bit field set to 1, a 0 if NONE of the rows has its bit field set to 1. To do this, we just tack on a rule to our WHERE clause.<BR><BR>SELECT * FROM Table1 WHERE &#060;something> AND BitField = 1<BR><BR>Now, if this query returns NOTHING, then the value is 0, if it returns something, then the value is 1! So, using ASP we can test:<BR><BR>if rs.EOF then bitwiseOR = 1 else bitwiseOR = 0<BR><BR>Make sense?

  3. #3
    Jed Farr Guest

    Default RE: SQL Question

    I think that I misled you by calling the field BitField, the field&#039s datatype is INT, and in my application it can be anything between 0 and 1024. I did receive a solution on the mssql mailing list (see www.swynk.com). It simplifies the stored procedure:<BR><BR>create table i(i int primary key)<BR><BR>go<BR><BR>insert i values (0)<BR>insert i values (1)<BR>insert i values (36)<BR>insert i values (37)<BR><BR>go<BR><BR>declare @i int<BR>select @i=0<BR><BR>select @i=@i &#124 i.i from i i<BR><BR>(From Alexey Eltsov)<BR><BR>Thanks very much for your help,<BR><BR>Jed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •