0

I have two tables in a SQL Server DB. One table BusinessOperations has various information about this business object, the other table OperationType is purely a bitwise flag table that looks like this:

| ID | Type    | BitFlag |
|  1 | Basic-A |   -2    |
|  2 | Basic   |   -1    |
|  3 | Type A  |   0001  |
|  4 | Type B  |   0002  |
|  5 | Type C  |   0004  |
|  6 | Type D  |   0008  |
|  7 | Type E  |   0016  |
|  8 | Type F  |   0032  |

The BitFlag column is a varchar column, the bitflags were inserted as '0001' as an example. In the BusinessOperations table, there's a column where the application that uses these tables updates it based on what is selected in the application's UI. As an example, I have one type which has the Basic,Type A, and Type B types selected. The column value in BusinessOperations is 3.

Based on this, I am trying to write a query which will show me something like this:

| ID | Name |  Description  |      OperationType    |
|  1 | Test |     Test      | Basic, Type A, Type B |

Here is the actual layout of the BusinessOperations table (Basic-A and Basic are bit columns:

| ID | Name |  Description  | Basic-A | Basic | OperationType |
|  1 | Test |     Test      |    0    |   1   |       3       |

There is nothing that relates these two tables to each other, so I cannot perform a join. I am very inexperienced with bitwise operations and am at a loss on how exactly to structure my select query which is being used to analyze this data. I feel like it needs a STUFF or CASE, but I don't know how I can get this to just show the types and not just the resultant BitFlag.

SELECT ID, Name, Description, OperationType
FROM OperationType
ORDER BY ID
Vistance
  • 345
  • 2
  • 4
  • 12
  • This is a little unclear. The BitFlag for the first two rows and the one at the end of your second paragraph are using integers. By your description, though, it's a `VARCHAR` column, and some of them are stored as zero-padded number strings instead. Any reason not to just store them as integers directly in the table? – xathien Apr 20 '15 at 20:09
  • Yeah I don't understand the logic of what you are trying to achieve at all. Why would the sample data in your question produce the desired results in your question? – Tab Alleman Apr 20 '15 at 20:20
  • @xathien The application would not accept the values as `INT` so they are stored as `VARCHAR`. Both `'-2'` and `'-1'` were also stored as `VARCHAR`. The intent is so that the application will know when it sees `Basic-A` or `Basic` to look at the bit columns and not the `OperationType` column when it sees those. This is all resultant because of the application which is legacy so as not to rewrite many lines of code where the `Basic-A` and `Basic` columns are used. – Vistance Apr 20 '15 at 20:22
  • You have two different issues to contend with. One is figuring out the mssql bitmask operators (select ... where operationtypecol & 2 > 0 )will flag the Type B operation for example. The second is to concatenate the hit operation - because you can have multiple operations you will probably need to loop through the bit positions and update your OperationType varchar columns. I don't think you can do what you want without looping. This type of issue, btw, is why compound values in sql columns are considered somewhat questionable in terms of form. Cleaner to have a child op table. – JL Peyret Apr 20 '15 at 20:26
  • 3
    Maybe you shouldn't be using bitmasking for this at all. It's not the 70s anymore, after all. [I've blogged about a performance problem with using this approach](http://sqlperformance.com/2012/08/t-sql-queries/dry-principle-bitwise-operations) but there are other logical problems, too. – Aaron Bertrand Apr 20 '15 at 20:28
  • This sounds like a legacy system with a UI already in place. I've seen this sort of poor design before unfortunately. – jzapata Apr 20 '15 at 20:34
  • Bitwise manipulation in SQL is not fun. If possible, I'd move this to a higher level... – Jamie F Apr 20 '15 at 20:34

2 Answers2

0

In the BusinessOperations table the Basic-A and Basic field are bit fields which is just another way of saying the value can only be a 1 or 0. Think of it like a boolean value True/False. So, in your query you can check each of those to determine whether to include 'Basic-A' and 'Basic' or not.

The OperationType is probably an id which you can lookup in the OperationsType table to get the Type and BitFlag. Without understanding your data completely it looks as if you could do a join for that part. Hopefully that is in the right general direction. If not, let me know.

jzapata
  • 1,229
  • 1
  • 12
  • 17
0

Since you're storing the flag in OperationType as a VARCHAR, the first thing you need to do to is CONVERT or CAST the string to a number so we can do proper bitwise comparisons. I'm slightly unfamiliar with SQL Server, but you may need to remove the leading zeroes before the cast. Thus, the OperationType column in our desired SQL will look something like

CONVERT(INT, BitFlag)

Then, comparing that to our OperationType column would look something like

CONVERT(INT, BitFlag) & OperationType

The full query would look something like (forgive my lack of SQL Server expertise again):

SELECT bo.ID, bo.Name, bo.Description, ot.Type
FROM BusinessOperations AS bo
JOIN OperationType AS ot
ON CONVERT(INT, ot.BitFlag) & OperationType <> 0

The above query will effectively get you a list of the OperationTypes. If you absolutely need them on one line, see other answers to learn how to emulate something like GROUP_CONCAT in SQL Server. Disclaimer: Joining on a bitmask gives no guarantee of performance.

The other problem this answer does not solve is that of your legacy Basic and Basic-A fields. Personally, I'd do one of two things:

  1. Remove them from the OperationType table and have the application tack the two on, based on the Basic and Basic-A columns as appropriate.
  2. Put Basic and Basic-A as their own, positive flags in the OperationType table, and have the application populate the legacy columns as well as the OperationType column as appropriate.

As Aaron Bertrand has said in the comments, this really isn't an issue for Bitmasking at all. Having a many-many table that associates BusinessOperations.ID to OperationType.ID would solve all your problems in a much better way.

Community
  • 1
  • 1
xathien
  • 812
  • 7
  • 10
  • This solved the main issue for me, much appreciated. I agree that I do not like this bitmask solution, but this was not something I chose to do but had to work with. The application currently already manually updates the other two columns, so what I'm dealing with is purely something when running select statements to return back the values. I get results now correctly for the number, but the default is 0 which then ignores the `Basic` and `Basic-A` columns even if they are checked. – Vistance Apr 21 '15 at 14:22