Row by row boolean comparison in PL/SQL?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Row by row boolean comparison in PL/SQL?

  1. #1
    Join Date
    Mar 2002
    Posts
    22

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2002
    Posts
    22
    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
  •  


Click Here to Expand Forum to Full Width