-
Row by row boolean comparison in PL/SQL?
Hi all,
I am trying to figure out if it is possible to do the following:
I am writing a message forum, and permissions in the forum are stored in a table 1 permission per column:
Code:
CREATE TABLE scc_bb_groups
(group_id NUMBER(*,0) NOT NULL,
forum_id NUMBER(*,0) NOT NULL,
group_view NUMBER(1,0),
group_read NUMBER(1,0),
group_post NUMBER(1,0),
group_reply NUMBER(1,0),
group_edit NUMBER(1,0),
group_delete NUMBER(1,0),
group_sticky NUMBER(1,0),
group_announce NUMBER(1,0),
group_vote NUMBER(1,0),
group_pollcreate NUMBER(1,0),
group_attachments NUMBER(1,0),
group_mod NUMBER(1,0),
CONSTRAINT SCC_BB_GROUPS_PK
PRIMARY KEY (group_id,forum_id));
Users are associated to groups (well, the group name table [not shown] actually), and groups can be associated to multiple forums.
Suppose a forum has two mod groups associated to it, and a given user is in both groups...
How could I collect both rows and OR them together (or however many rows there are)?
I can do this in PHP (and already have), but it would be nice to just pass in an id for a user and get all the groups they are in for a given forum and get s single row back per forum.
To clarify (with a simpler table than above):
Code:
grp_id forum_id view edit reply post
1 2 1 1 0 1
2 2 0 1 1 0
2 4 0 0 0 0
6 5 1 0 0 1
7 5 0 0 1 0
Based on the above, the results I would like back from the query are (I will need to use an IN OUT REF CURSOR to get the results back to PHP):
Code:
forum_id view edit reply post
2 1 1 1 1
4 0 0 0 0
5 1 0 1 1
Would anyone be able to suggest how to write this in PL/SQL (if it can be?)
Thanks in advance for any help!
Cheers,
Keith.
-
The MAX() function will do it ...
Code:
select
forum_id,
Max(group_view) group_view,
Max(group_read) group_read,
Max(group_post) group_post,
...
Where
group_id = 2
Group By
forum_id
... for example
-
Dave,
Talk about not seeing a twig for the log in my eye!
I had convinced myself that because I did it with a loop and boolean ORing in PHP, it had to be the same in PL/SQL as well. Lesson learned!
Thanks for the (simple) solution!
Cheers,
Keith.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|