Home » SQL & PL/SQL » SQL & PL/SQL » Need Help in Query (merged) (Oracle 12.1.2.0)
Need Help in Query (merged) [message #681550] Sat, 01 August 2020 11:38 Go to next message
vinodkumarn
Messages: 59
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 Go to previous message
Michel Cadot
Messages: 67449
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:40
Thank you very much for the answer and sorry for not following the rules for posting.

Michel Cadot wrote on Sat, 24 March 2012 07:17
No 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.

Previous Topic: How to get a Output as mentioned below
Next Topic: set default values to current epoch (2 merged)
Goto Forum:
  


Current Time: Thu Oct 22 12:52:38 CDT 2020