Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: Aggregate bitwise OR

    I was doing a SUM on my returned rows and i found that what i really want is an aggregate bitwise OR on all the returned rows. Do you know what's the function for that?

    for example:

    SELECT BitwiseOR(Numbers)
    FROM NumbersTable

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bitwise operations are not aggregates operations. They work across columns in a single record at a time.

    I think you will need to write a procedure that loops through your records and accumulates the results of your OR operations.

    blindman

  3. #3
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by blindman
    Bitwise operations are not aggregates operations. They work across columns in a single record at a time.

    I think you will need to write a procedure that loops through your records and accumulates the results of your OR operations.

    blindman
    Im thinking of using a user defind function to do that but the problem is how would i make the function to process all the records and not a record at a time. i.e. how to make it an aggregate function.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't make a user-defined aggregate function.

    It would be cool if you could!

    blindman

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    By the way, are you sure you want a bitwise OR? Or do you want some sort of logical "or"?

    A bitwise OR applied across any substantial number of values would likely just return 2 to some power, wouldn't it?

    blindman

  6. #6
    Join Date
    Nov 2003
    Posts
    94
    create table x
    (
    vals int not null
    )
    insert into x values(1)
    insert into x values(2)
    insert into x values(3)
    insert into x values(4)
    insert into x values(5)
    insert into x values(6)
    insert into x values(7)

    declare @sum int
    set @sum = 0

    declare @bit bigint

    set @bit = cast(0x80000000 as bigint)
    while @bit > 0
    begin
    if exists (
    select vals
    from x
    where (cast(vals as bigint) & @bit) != 0
    )
    set @sum = @sum | cast(@bit as int)
    set @bit = @bit / 2
    end

    select @sum
    select 1 | 2 | 3 | 4 | 5 | 6 | 7


    drop table x

  7. #7
    Join Date
    Dec 2003
    Posts
    9

    Thumbs up Salutations

    wow nice code..very impressive.

    one thing remains. how can i convert this into a function where table x and its values will be fed as an input paramater to the function and not predefined. is this doable?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not without dynamic code. Your best bet is still to write a stored procedure that loops through the table (using HanafiH's logic, for instance) and calculates a running sum. If you do it in a procedure you can pass the name of the table and field as parameters, have the procedure create an appropriate SQL statement and then execute the statement.

    blindman

  9. #9
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by blindman
    Not without dynamic code. Your best bet is still to write a stored procedure that loops through the table (using HanafiH's logic, for instance) and calculates a running sum. If you do it in a procedure you can pass the name of the table and field as parameters, have the procedure create an appropriate SQL statement and then execute the statement.

    blindman
    but the thing is i don't have a name for a table. the table i want to pass is generated using SELECT statement. should i give up?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pass the result of your select statement as a parameter, or include the select statement in your procedure and assign the result to a variable that you fold into your SQL string.

    Should you give up? That's up to you. This is not SQL Programming 101 that you are trying.

    blindman

  11. #11
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by blindman
    Pass the result of your select statement as a parameter
    what would be the type of this parameter? table? can you give me an example.

    Thank you

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok, trying to follow...

    can you tell me WHY you want to do this?

    Got to be an easier way..

    what's the business reason?

    Or is this an academic exercise?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by Brett Kaiser
    Ok, trying to follow...

    can you tell me WHY you want to do this?

    Got to be an easier way..

    what's the business reason?

    Or is this an academic exercise?
    I'm implementing user/group permissions using bit operations. the permissions are: READ =1 , WRITE =2, DELETE=4. Now i have a GroupPermission table wich associates each group with the permissions. for example group USERS has the permission value of 5 (READ and DELETE).

    The procedure im trying to build is to retrieve the permissions for each user. for example i have a user Daemon who's member of the Admin group and also the User group. The Admin group permission is 7 and User goup is 5. Now to get the permissions of user Daemon i should OR 7 and 5 and get 7.

    that's it. simple eh?

    ---
    my storedprocedure

    CREATE PROCEDURE GetUserPermissions
    (
    @UserID int
    )
    AS

    SELECT Permission
    FROM GroupPermission
    INNER JOIN Groups ON GroupPermission.GroupID = Group.GroupID
    INNER JOIN UserGroup ON Group.GroupID = UserGroup.GroupID
    INNER JOIN Users ON UserGroup.UserID = Users.UserID
    WHERE Users.UserID = @UserID


    ther result i would be getting for user Daemon is

    Permission
    ------------
    7
    5

    now if i just had an aggregate BitwiseOR i would've done

    Select BitwiseOR(Permission)
    -- the rest of the query

    and get

    Permission
    ------------
    7
    Last edited by Daemon74; 12-04-03 at 15:40.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting challenge. This should work up to values of 15. You can extend it to 8 or more bits if you want.

    declare @Testvalue int
    set @testValue = 2

    declare @PermissionTable Table
    (RoleID int,
    Permission int)

    insert into @Permissiontable (RoleID, Permission) Values (1, 3)
    insert into @Permissiontable (RoleID, Permission) Values (1, 5)
    insert into @Permissiontable (RoleID, Permission) Values (2, 1)
    insert into @Permissiontable (RoleID, Permission) Values (2, 3)

    select RoleID,
    cast(sum(Permission & 8) as bit) * 8
    + cast(sum(Permission & 4) as bit) * 4
    + cast(sum(Permission & 2) as bit) * 2
    + cast(sum(Permission & 1) as bit) TotalPermission
    from @PermissionTable
    group by RoleID

    blindman

  15. #15
    Join Date
    Dec 2003
    Posts
    9

    Cool You're the man

    Work's like a charm!! Thank you

    I could see and now im blind (like blindman)

Posting Permissions

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