Get Started with Redwood for Oracle Cloud HCM Begin Now
To ensure that questions get required attention from community members and are NOT left unanswered, it’s important for the author to indicate (by selecting “Yes” or “No” when prompted) whether the question was answered. (newly added) Please note that it is also important to respond to EACH comment your question receives. Your Yes or No response ensures an accurate status for your question.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
Duplicate Rows
Summary
duplicate rows with left outer joinContent
Hello. I need to build a report that returns benefit information for everyone with a column indicating if someone is on a LOA and the dates of the LOA.
When someone is on a LOA, I am getting two rows, one with the LOA information and one without. If I use an inner join for the absence column, I only get results for employees that are on a LOA so I wrote it as a outer join. I have attached the code and an example of the results.
Code Snippet
SELECT DISTINCT PAPF.PERSON_NUMBER PERSON_NUMBER ,ENROLL.person_ID , TO_CHAR(ENROLL.ENRT_CVG_STRT_DT , 'MM/DD/YYYY') EFFECTIVE_START_DATE , TO_CHAR(ENROLL.ENRT_CVG_THRU_DT , 'MM/DD/YYYY') EFFECTIVE_END_DATE , TO_CHAR(ENROLL.LAST_UPDATE_DATE , 'MM/DD/YYYY') LAST_UPDATED_DATE ,PER_EMAIL_ADDRESSES.EMAIL_ADDRESS ,PER_PHONES.SEARCH_PHONE_NUMBER ,PL_TYP.NAME PLAN_TYPE ,plan.NAME PLAN_NAME ,PAAM.ASSIGNMENT_NUMBER ASSIGN_NUMBER ,PAAM.ASSIGNMENT_STATUS_TYPE ASSIGN_STATUS , PA.POSTAL_CODE POSTAL_CODE ,PA.ADDRESS_LINE_1 ADDRESS_LINE_1 ,PA.ADDRESS_LINE_2 ADDRESS_LINE_2 ,PA.TOWN_OR_CITY CITY ,PA.REGION_3 PROVINCE , PER_NAME.FIRST_NAME FIRST_NAME , PER_NAME.LAST_NAME LAST_NAME ,LEG_NAME.NAME LEGAL_EMPLOYER , TO_CHAR(PPOS.ACTUAL_TERMINATION_DATE , 'MM/DD/YYYY') TERMINATION_DATE , TO_CHAR(PPOS.DATE_START , 'MM/DD/YYYY') HIRE_DATE , ANC_ABSENCE_TYPES_VL.name ABSENCE_TYPE , TO_CHAR(ANC_PER_ABS_ENTRIES.start_date,'MM/DD/YYYY') ABSENCE_START_DATE , TO_CHAR(ANC_PER_ABS_ENTRIES.end_date, 'MM/DD/YYYY') ABSENCE_END_DATE ,paam.bargaining_unit_code Bargaining_Unit FROM PER_ALL_PEOPLE_F PAPF LEFT JOIN PER_ADDRESSES_F PA ON PAPF.MAILING_ADDRESS_ID = PA.ADDRESS_ID and TRUNC(SYSDATE) BETWEEN pa.EFFECTIVE_START_DATE AND pa.EFFECTIVE_END_DATE INNER JOIN BEN_PRTT_ENRT_RSLT ENROLL ON ENROLL.PERSON_ID = PAPF.PERSON_ID AND ENROLL.prtt_enrt_rslt_stat_cd IS NULL AND ENROLL.sspndd_flag <> 'Y' INNER JOIN PER_EMAIL_ADDRESSES ON PAPF.PERSON_ID = PER_EMAIL_ADDRESSES.PERSON_ID INNER JOIN PER_PHONES ON PAPF.PRIMARY_PHONE_ID = PER_PHONES.PHONE_ID INNER JOIN BEN_PL_TYP_F
Tagged:
0