Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

report on user account details - Role Names

211
Views
4
Comments
Peter Kirwan
Peter Kirwan Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

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

Tagged:

Comments

  • Tim Welsh
    Tim Welsh Rank 3 - Community Apprentice

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

  • Prudence K
    Prudence K Rank 5 - Community Champion

    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 Rank 6 - Analytics Lead

    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
    Ramesh Nakka Rank 4 - Community Specialist

    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