1. Registered User
Join Date
Dec 2003
Posts
9

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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
You can't make a user-defined aggregate function.

It would be cool if you could!

blindman

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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. Registered User
Join Date
Dec 2003
Posts
9

## 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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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. Window Washer
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..

Or is this an academic exercise?

13. Registered User
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..

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
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
Join Date
Dec 2003
Posts
9

## 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
•