CODEWITHSUNDEEP
oracle-database
Peck
Peck

Reputation: 43

Oracle temporary table report creation

Temporary tables report creation

Users table

User id  User name 
1         user1
2         user2
3         user3

USER_IN_ROLES table

USRINR_USER_ID   USRINR_ROLES_ID

1                        103
3                        104
2                        105
1                        107
2                        108
1                        105
1                        108

roles rl

Roles ID                 Description
104                      User management
107                      Modify
103                      Debit
105                      General
108                      Audit

Relation User id between table 1 & 2 and roles id between 2 & 3

I would like a query that will give an op like this 

UName   FirstEntitl     Second Entitl
user1    Team           Debit
user3   Security admin  User Management
user2   Read only user  General
user1   Team            Modify
user2   Read only user  Audit
user1   Team            General
user1   Team            Audit

So all users who have user management as role( no other roles) will be Security admin A users who have modify role will be team and if a user doesn't have modify and user management is a read only user. User 1 is TEAM so when he has another entitlement as General still column 2 should have him as team. IF user has Modify then all the other rows for the user should have Team. USer is a Reead only user as he doesnt have Modify anywhere. User 3 is little easy he is Sec Admin if he has User Management.

Upvotes: 1

Views: 112

Answers (1)

Jean-François Savard
Jean-François Savard

Reputation: 21004

If I understand your question properly, I think this simple query would do it

SELECT  a.user_name as UName,
        decode(count_of_roles.nbRoles,
               1, decode(b.id,
                        107, 'Team', -- Display team as it is the only role
                        104, 'Security admin', -- Display Security admin as it is the only role
                        'Read only user'), -- If only one role was found, but neither team or admin we display Read only user 
               'Read only user') as "First Entitl", -- Display Read only user if more then one role was found
        c.description as "Second Entitl"
FROM users a
JOIN users_in_roles b on a.user_id = b.usrinr_user_id
JOIN roles_rl c on c.roles_id = b.usrinr_roles_id
JOIN (SELECT a.user_id, count(b.usrinr_roles_id) as nbRoles -- Join on a result set giving the nb of roles for the current user.
       FROM users a
       JOIN user_in_roles b ON a.user_id = b.usrinr_roles_id
       GROUP BY a.user_id) count_of_roles ON a.user_id = count_of_roles.user_id;

Upvotes: 1

Related Questions