Post reply

compare two bitmasks in sql

  • October 3, 2011 at 12:23 am

    #390529

    Hi All,

    Iam having a usersroles table with 2 columns USerID and RoleID where RoleID Consists of bitmask values.And when i ran the below query the output shows as below :

    select UserID,(roleid) from dbo.Usersroles where UserID = 23

    UserID RoleID

    ---------

    232

    233

    which means that a single user is having 2 roleID's hence when we use the below query the output will be as follows using SUM:

    select UserID , SUM(roleid) as RoleID from dbo.Usersroles where UserID = 23

    group by UserID :

    UserID RoleID

    -----------

    235

    But i want the query that compares the bitmask values and shows the RoleID as 3 as 2(bitmask) already exists in 3(bitmask) for single user

    Thanks,

    Ram

  • October 3, 2011 at 12:59 am

    #1389302

    SqlServer has no bit-wise aggregations but something like this should do you fine

    select UserID , Max(roleid&1)+

    Max(roleid&2)+

    Max(roleid&4)

    from

    #UserRoles


    Clear Sky SQL
    My Blog[/url]

  • October 3, 2011 at 1:16 am

    #1389307

    Hi Dave,

    Thanks for your quick reply. Iam having one doubt here what if the roleid for that particular users are 2 & 6, then how to write the query as the otput should be shown as 6 then. Kindly guide me on this.

    Thanks,

    Ram

  • October 3, 2011 at 1:36 am

    #1389321

    Hi Dave,

    Here if the roleid for the user are 4 and 15 it is showing the output as 7 instead of 15. Could you please verify and send me the correct query plz..

  • October 3, 2011 at 2:10 am

    #1389335

    Hi Dave,

    I got the answer that i was looking for..its working for me great..

    Thanks,

    Ram

  • October 3, 2011 at 2:40 am

    #1389342

    Glad you found your answer..

    BTW , using an ID like this is quite bad practice. If it were me , i would have this as data on a child table.


    Clear Sky SQL
    My Blog[/url]

  • October 3, 2011 at 2:42 am

    #1389345

    Thanks for your valuable suggestion Dave. You Made my day really..Could you give me some good example on this..

  • October 3, 2011 at 2:45 am

    #1389346

    Iam have one more way to get the answer :

    DECLARE @counter INT=0

    SELECT @counter = @counter |CONVERT(INT, RoleID) from UsersRoles where UserID = 23

    select @counter as Result

    Could you help me out how to get the same answer in the query without passing a parameter.

    That would be a great favour for me.

    Thanks,

    Ram

  • October 3, 2011 at 3:39 am

    #1389375

    rams.prsk (10/3/2011)

    Thanks for your valuable suggestion Dave. You Made my day really..Could you give me some good example on this..

    So...

    My 'Roles' table would be...

    RoleID integer PK,

    IsRoleAdmin char(1) Not null,

    IsRoleAccounts char(1) Not null,

    IsUser....... etc

    This is 'better' as

    A) 'ID's only point to a row and should have no meaning.

    B) You can now ,potentially, index on each individual Y/N condition

    As to the parameter question , you cant the logic will require something , there is no bitwise aggregation in SQLServer.


    Clear Sky SQL
    My Blog[/url]

  • October 3, 2011 at 3:43 am

    #1389376

    Thank you very much dave 🙂 U made my day today.. This is my early stages into sql and iam getting so much needfull from u which will help me to grow a lot 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply