Oracle Transactional Business Intelligence

Products Banner

report on user account details - Role Names

86
Views
4
Comments

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

  • Tim Welsh
    Tim Welsh ✭✭✭

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

  • Prudence K
    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
    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 ?

  • 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