| Need Help in Query (merged) [message #681550] |
Sat, 01 August 2020 11:38  |
vinodkumarn
Messages: 60
Registered: March 2005
|
Member |
|
|
Requirement:
Display only one role value for each user, if user have multiple DB role value, then display PAD_ADMIN first,
if not then PERFORMANCE_ROLE. If user do not have either, display “NONE”
I tried below query and is not working
select au.user_id, drp.granted_role
from DBA_ROLE_PRIVS_TEST drp,ref_ausersec_profile au,perf_accountability perf
where drp.grantee=au.user_id
and au.orgcode = perf.orgcode(+)
and au.subsystem = 'PAD'
and
case when drp.granted_role in ('PAD_ADMIN') then 1
when drp.granted_role in ('PERFORMANCE_ROLE') and drp.granted_role not in ('PAD_ADMIN') then 1
else 0
END = 1
Result now showing 4 records as below (just padded |||| to differentiate column values)
HELEN_MILLER||||PERFORMANCE_ROLE
VIKAS_DUBEY||||PERFORMANCE_ROLE
NATASHA_PAUL||||PAD_ADMIN
NATASHA_PAUL||||PERFORMANCE_ROLE
I want the NATASHA_PAUL record to come only once having PAD_ADMIN role
|
|
|
|
| Re: Need Help in Query (merged) [message #681552 is a reply to message #681550] |
Sat, 01 August 2020 12:29  |
 |
Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Fri, 23 March 2012 20:54...
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...
vinodkumarn wrote on Fri, 23 March 2012 21:40Thank you very much for the answer and sorry for not following the rules for posting.
Michel Cadot wrote on Sat, 24 March 2012 07:17No problem for this time but you will have no excuse for the next question.
Thanks for the feedback,
Michel
Have a look at ROW_NUMBER function.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
