nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

report on user account details - Role Names

Peter Kirwan

Summary

SQL for reporting on employees roles

Content

Can someone see how to stop the multiple rows from returning in below SQL?

select "PER_USERS"."USERNAME" as "USERNAME",
"PER_ROLES_DN_VL"."ROLE_NAME" as "ROLE_NAME",
"PER_ALL_PEOPLE_F"."ATTRIBUTE3" as "ATTRIBUTE3",
"PER_PERSON_NAMES_F"."FIRST_NAME" as "FIRST_NAME",
"PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",
"PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",
"PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE"
from "FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",
"FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F",
"FUSION"."PER_ROLES_DN_VL" "PER_ROLES_DN_VL",
"FUSION"."PER_USER_ROLES" "PER_USER_ROLES",
"FUSION"."PER_USERS" "PER_USERS"
where "PER_USERS"."USER_ID"="PER_USER_ROLES"."USER_ID"
and "PER_USER_ROLES"."ROLE_ID"="PER_ROLES_DN_VL"."ROLE_ID"
and "PER_USERS"."PERSON_ID"="PER_ALL_PEOPLE_F"."PERSON_ID"
and "PER_ALL_PEOPLE_F"."PERSON_ID"="PER_PERSON_NAMES_F"."PERSON_ID"
and "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" >CURRENT_DATE
order by "PER_USERS"."USERNAME" ASC

Find more posts tagged with

Reporting

Answers

BI Publisher Data Models

OTBI

BI Publisher Report Templates

Comments

Tim Welsh

Simplest way might be to add DISTINCT after the word select. So, Select distinct "PER_USERS"...

Prudence K

Peter - is it possible that your users would have more than 1 effective start/end date? Can you put in a MAX/MIN statement there?

RichardChan

It is because a user is duplicated somewhere, possibly they have had more than one role, maybe select the role with the maximum effective date i.e. the most recent role ?

Ramesh Nakka

Hi Peter,

First you would need to identify which fields could give multiple rows then accordingly you would need to apply optimized filters to retrieve only latest values.

# In most of the cases one can Candidate can have multiple offers and multiple offer start dates. So please include condition to display only latest offer.

Similarly check for any other fields which has multiple values/resulted in multiple rows.

Regards,

Ramesh Nakka