CODEWITHSUNDEEP
sqlplsql
user3236074
user3236074

Reputation: 5

SQL Procedure to return all values of one table and YES/NO if present in another

I have the following tables: ROLE, PRIVILEGE and ROLE_PRIV. Every role and every privilege has a unique ID and name, while ROLE_PRIV contains the link between the two tables, which indicates the privileges of every role (with every role ID+priv ID combination being unique).

I am trying to generate a report where I will have a column with ALL privileges and the top row listing all the role IDs and then having a YES/NO in the cells below, indicating whether this role has the specific privilege assigned.

I would be extremely grateful to anyone who can give me some guidance on whether I can create a query or PL/SQL procedure to set this report up. I am fine with getting the info for only one role as well, because they are usually not more than 10 and repeating the query/procedure 10 times is not going to be a problem for me.

I have moderate experience with SQL and very little experience with PL/SQL, but enough to patch something up if given good pointers.

Thanks for taking the time to read this. :)

Upvotes: 0

Views: 1164

Answers (1)

Mikhail
Mikhail

Reputation: 1560

If you're using 11g or later version of Oracle, the easiest way to transpose rows into columns is PIVOT operator (documentation). For example, consider this query - it'll give you the table of all roles and privileges with corresponding values for them:

SELECT * FROM
(SELECT fj.role_name, fj.priv_name, DECODE(rp.val,1,'YES',0,'NO','-') VAL
FROM (SELECT r.id role_id, r.name role_name, p.id priv_id, p.name priv_name
      FROM ROLE r, PRIVILEGE p) fj
      LEFT JOIN ROLE_PRIV rp ON fj.role_id = rp.role_id AND fj.priv_id = rp.priv_id)
PIVOT (MAX(VAL) FOR PRIV_NAME IN (list_of_privileges))
ORDER BY ROLE_NAME

But there is a catch with such solution: you need to name each privilige in IN clause (or use XML keyword, but it'll mess up the result), but, considering that list of priviliges supposed to change not so frequently, it shouldn't be a problem. The following example:

WITH ROLE AS (SELECT 1 ID, 'ROLE1' NAME FROM DUAL
              UNION
              SELECT 2 ID, 'ROLE2' NAME FROM DUAL
              UNION
              SELECT 3 ID, 'ROLE3' NAME FROM DUAL),
     PRIVILEGE AS (SELECT 1 ID, 'READ' NAME FROM DUAL
                   UNION
                   SELECT 2 ID, 'WRITE' NAME FROM DUAL
                   UNION
                   SELECT 3 ID, 'EXECUTE' NAME FROM DUAL),
     ROLE_PRIV AS (SELECT 1 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL
                   UNION
                   SELECT 1 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL
                   UNION
                   SELECT 2 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL
                   UNION
                   SELECT 2 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL
                   UNION
                   SELECT 3 ROLE_ID, 1 PRIV_ID, 0 VAL FROM DUAL
                   UNION
                   SELECT 3 ROLE_ID, 3 PRIV_ID, 1 VAL FROM DUAL)
SELECT * FROM
(SELECT fj.role_name, fj.priv_name, DECODE(rp.val,1,'YES',0,'NO','-') VAL
FROM (SELECT r.id role_id, r.name role_name, p.id priv_id, p.name priv_name
      FROM ROLE r, PRIVILEGE p) fj
      LEFT JOIN ROLE_PRIV rp ON fj.role_id = rp.role_id AND fj.priv_id = rp.priv_id)
PIVOT (MAX(VAL) FOR PRIV_NAME IN ('READ','WRITE','EXECUTE'))
ORDER BY ROLE_NAME

Will give you the following result:

ROLE_NAME   'READ'  'WRITE' 'EXECUTE'
ROLE1   YES NO  -
ROLE2   YES NO  -
ROLE3   NO  -   YES

If you're using the earlier version of Oracle, you can consider alternative solutions (like this one).

Well, if you can't use PIVOT operation, the simplest way to get the desired output would be this query:

SELECT p.name, DECODE((SELECT VAL 
                       FROM ROLE_PRIV rp
                       WHERE rp.role_id = (SELECT ID 
                                           FROM ROLE r
                                           WHERE r.name = :ROLE_NAME)
                             AND rp.priv_id = p.id),
                       1, 'YES',
                       0, 'NO',
                       '-') :ROLE_NAME
FROM PRIVILEGE p

You can add addtional roles to this query by just adding another column. For example, take a look at this query:

WITH ROLE AS (SELECT 1 ID, 'ROLE1' NAME FROM DUAL
              UNION
              SELECT 2 ID, 'ROLE2' NAME FROM DUAL
              UNION
              SELECT 3 ID, 'ROLE3' NAME FROM DUAL),
     PRIVILEGE AS (SELECT 1 ID, 'READ' NAME FROM DUAL
                   UNION
                   SELECT 2 ID, 'WRITE' NAME FROM DUAL
                   UNION
                   SELECT 3 ID, 'EXECUTE' NAME FROM DUAL),
     ROLE_PRIV AS (SELECT 1 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL
                   UNION
                   SELECT 1 ROLE_ID, 2 PRIV_ID, 0 VAL FROM DUAL
                   UNION
                   SELECT 2 ROLE_ID, 1 PRIV_ID, 1 VAL FROM DUAL
                   UNION
                   SELECT 2 ROLE_ID, 2 PRIV_ID, 1 VAL FROM DUAL
                   UNION
                   SELECT 3 ROLE_ID, 1 PRIV_ID, 0 VAL FROM DUAL
                   UNION
                   SELECT 3 ROLE_ID, 3 PRIV_ID, 1 VAL FROM DUAL)
SELECT p.name, 
       DECODE((SELECT VAL 
               FROM ROLE_PRIV rp
               WHERE rp.role_id = (SELECT ID 
                                   FROM ROLE r
                                   WHERE r.name = 'ROLE1')
                     AND rp.priv_id = p.id),
              1, 'YES',
              0, 'NO',
              '-') ROLE1,
       DECODE((SELECT VAL 
               FROM ROLE_PRIV rp
               WHERE rp.role_id = (SELECT ID 
                                   FROM ROLE r
                                   WHERE r.name = 'ROLE2')
                     AND rp.priv_id = p.id),
              1, 'YES',
              0, 'NO',
              '-') ROLE2,
       DECODE((SELECT VAL 
               FROM ROLE_PRIV rp
               WHERE rp.role_id = (SELECT ID 
                                   FROM ROLE r
                                   WHERE r.name = 'ROLE3')
                     AND rp.priv_id = p.id),
              1, 'YES',
              0, 'NO',
              '-') ROLE3
FROM PRIVILEGE p

It will provide you result for three roles.

Something like this should work:

SELECT p.name, 
       DECODE((SELECT COUNT(1)
               FROM ROLE_PRIV rp
               WHERE rp.role_id = (SELECT ID 
                                   FROM ROLE r
                                   WHERE r.name = 'ROLE1')
                     AND rp.priv_id = p.id),
              0, 'NO',
              'YES') ROLE1
FROM PRIVILEGE p

Upvotes: 2

Related Questions