General OBIEE

Get Involved. Join the Conversation.

Topic

    Peter Kirwan
    report on user account details - Role Names
    Topic posted May 12, 2017 by Peter KirwanBlue Ribbon: 750+ Points, tagged BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Core HR, Data Models, Fusion, OBI Answers, OBIEE Answers, OTBI, Reports, SQL 
    92 Views, 4 Comments
    Title:
    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

    Comment

     

    • Tim Welsh

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

    • Prudence Kruchten

      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?

    • Richard Chan

      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