DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002

    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:

    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),
     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):
    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):

    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!


  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    The MAX() function will do it ...

       Max(group_view) group_view,
       Max(group_read) group_read,
       Max(group_post) group_post,
       group_id = 2
    Group By
    ... 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

    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!


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.