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 youBitwise 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|||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.|||You can't make a user-defined aggregate function.

It would be cool if you could!

blindman|||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|||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|||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?|||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|||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?|||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|||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|||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?|||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|||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|||Work's like a charm!! Thank you

I could see and now im blind (like blindman;))|||Originally posted by Daemon74
Work's like a charm!! Thank you

I could see and now im blind (like blindman;))

yeah...the more you know...

But really...implementing security? ahhh...why not use sql server roles?

Or are you using connection pooling?

Plus you're limiting yourself?

Plus I don't see you using the concept of roles either?

easier to place people in and out of roles as needed...and to simply chage the properties of a role...|||Originally posted by Brett Kaiser
yeah...the more you know...

But really...implementing security? ahhh...why not use sql server roles?

Or are you using connection pooling?

Plus you're limiting yourself?

Plus I don't see you using the concept of roles either?

easier to place people in and out of roles as needed...and to simply chage the properties of a role...

im using it for a web application like this forum where different parts have different permissions.. how would i use sql roles??|||Yeah, no...you use 1 id to connect to sql?

Why worry about all of this bit stuff though?

Why not just mimic sql server security on the database? Like create a User table, a role table, ect...|||Well that's exactly what i'm doing. User table, role table and permissions table.