Categories
report on user account details - Role Names
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
Comments
-
Simplest way might be to add DISTINCT after the word select. So, Select distinct "PER_USERS"...
0 -
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?
0 -
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 ?
0 -
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
0
