SELECT
PAPF.PERSON_NUMBER as PERSONNUMBER,
PNI.NATIONAL_IDENTIFIER_NUMBER as NATIONALIDENTIFIER,
PPNFV.FIRST_NAME as FIRSTNAME,
PPNFV.MIDDLE_NAMES as MIDDLENAME,
PPNFV.LAST_NAME as LASTNAME,
PAAM.ASSIGNMENT_STATUS_TYPE as EMPLOYMENTSTATUS,
PPOS.DATE_START AS HIREDATE,
PPOS.ORIGINAL_DATE_OF_HIRE as WORKRELATIONSHIPSTARTDATE,
PPOS.ACTUAL_TERMINATION_DATE as TERMINATIONDATE,
PP.DATE_OF_BIRTH as DOB,
PPLF.SEX as GENDER,
PAAM.EMPLOYMENT_CATEGORY as ASSIGNMENTCATEGORY,
PJLG.INFORMATION1 as FLSA,
CS.SALARY_BASIS_CODE as HOURLYSALARY,
paam.effective_start_date as ASSIGNMENTSTARTDATE,
PAAM.ASSIGNMENT_NAME as JOBTITLE,
PJFV.JOB_CODE as JOBCODE,
NVL(email.email_address,'none') as EMAIL,
CS.SALARY_AMOUNT as PAY,
CS.SALARY_BASIS_CODE as PAYTYPE,
CS.DATE_FROM as PAYEFFECTIVEDATE,
PAF.ADDRESS_LINE_1 as HOMEADDRESS1,
PAF.ADDRESS_LINE_2 as HOMEADDRESS2,
PAF.TOWN_OR_CITY as HOMECITY,
PAF.REGION_2 as HOMESTATE,
PAF.POSTAL_CODE as HOMEPOSTALCODE,
HLA.ADDRESS_LINE_1 as WORKADDRESS1,
HLA.ADDRESS_LINE_2 as WORKADDRESS2,
HLA.TOWN_OR_CITY as WORKCITY,
HLA.REGION_2 as WORKSTATE,
HLA.POSTAL_CODE as WORKPOSTALCODE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERIODS_OF_SERVICE PPOS,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERSONS PP,
PER_NATIONAL_IDENTIFIERS PNI,
PER_PERSON_NAMES_F_V PPNFV,
PER_JOBS_F_V PJFV,
PER_PEOPLE_LEGISLATIVE_F PPLF,
PER_ADDRESSES_F PAF,
HR_LOCATIONS_ALL HLA,
PER_JOB_LEG_F PJLG,
PER_EMAIL_ADDRESSES EMAIL,
CMP_SALARY CS,
PER_PERSON_ADDR_USAGES_F PPAUF
WHERE
cs.assignment_id = paam.assignment_id
AND paam.effective_start_date between cs.date_from and cs.date_to
AND ppnfv.name_type = 'GLOBAL'
AND paam.assignment_type = 'E'
AND paam.primary_flag = 'Y'
AND paam.job_id = pjfv.job_id
AND email.email_type = 'W1'
AND papf.person_id = ppnfv.person_id
AND PNI.person_id = paam.person_id
AND paam.person_id = pplf.person_id
AND paam.job_id = pjlg.job_id
AND ppauf.person_id = papf.person_id
AND ppauf.address_type = 'HOME'
AND ppauf.address_id = paf.address_id
AND pp.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date and paam.effective_end_date
AND paam.period_of_service_id = ppos.period_of_service_id
AND papf.person_id = paam.person_id
AND ppnfv.person_id = paam.person_id
AND hla.location_id = paam.location_id
AND pjlg.job_id = paam.job_id
AND papf.person_id = email.person_id
AND TRUNC(SYSDATE) BETWEEN cs.date_from and cs.date_to
AND TRUNC(SYSDATE) BETWEEN ppnfv.effective_start_date and ppnfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date and papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date and paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pjfv.effective_start_date and pjfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN hla.effective_start_date and hla.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pjlg.effective_start_date and pjlg.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppauf.effective_start_date and ppauf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date and pp.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pni.effective_start_date and pni.effective_end_date
ORDER BY PAPF.PERSON_NUMBER
Hi All! Returning multiple rows for each person and cannot figure out why. Not hitting any syntax errors, it's just that I'm returning multiple rows for each user (which has the downstream effect of timing out any reports I tie to this query.) Anyone have any ideas about how to resolve?
Thanks!
Each one of your tables should have a primary key column. Add those to your output. Add a where clause to select one person from the results. You should then be able to find the column with different values on each output row to determine which table is causing the problem.