Removing duplicate records

[OmenChild]

Hello all,
I am running a query, and it is returning a lot of duplicate records. Now when I change up the way I am joining the tables, I end up losing records. So I resolve the duplication issue, but I lose data. I have tried every combination of joins I could think of, and either I receive duplicate records, or I am missing records. Here is the code:

SET SCHEMA PSADM;

SELECT C.PER_STATUS, A.NAME, B.EMPLID, B.EMPL_RCD, B.DUR, B.ACTUAL_PUB_DATE, B.SEQ_NBR, B.TRC, B.TL_QUANTITY, B.LOCALITY,
B.COMP_RATECD, B.LBR_DIST_AMT, B.PAYABLE_STATUS, B.COMPANY, B.RECORD_ONLY_ADJ, B.OPRID
FROM PS_PERSONAL A INNER JOIN PS_PAYABLE_TIME B
ON A.EMPLID = B.EMPLID
LEFT OUTER JOIN PS_EMPLOYEES_DATA C
ON B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
WHERE B.RECORD_ONLY_ADJ = 'Y'
AND B.ACTUAL_PUB_DATE BETWEEN '2007-10-01' AND CURRENT DATE
WITH UR;

This join returns the duplicate records. Any ideas, suggestions?

 

[fredericofonseca]

Without knowing if your joins are correct (which if not may be the cause of it), the following MAY work.

SELECT DISTINCT C.PER_STATUS, A.NAME, B.EMPLID, B.EMPL_RCD, B.DUR, B.ACTUAL_PUB_DATE, B.SEQ_NBR, B.TRC, B.TL_QUANTITY, B.LOCALITY,
B.COMP_RATECD, B.LBR_DIST_AMT, B.PAYABLE_STATUS, B.COMPANY, B.RECORD_ONLY_ADJ, B.OPRID
FROM PS_PERSONAL A
INNER JOIN PS_PAYABLE_TIME B
ON A.EMPLID = B.EMPLID
LEFT OUTER JOIN PS_EMPLOYEES_DATA C
ON B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
WHERE B.RECORD_ONLY_ADJ = 'Y'
AND B.ACTUAL_PUB_DATE BETWEEN '2007-10-01' AND CURRENT DATE
WITH UR;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[notadba]

As per previous response, without knowing your data...

It looks like you are not getting "duplicate" rows from your data, as the employees table contains one record per employment instance. Since you are joining on the employee id and record instance, it seems that the issue comes from your payable_time table with the date range criteria: '2007-10-01' AND CURRENT DATE

Check the data in the columns coming from payable time, and it is likely that you either have multiple sequences for the actual_pub_date and/or multiple dates within the period.

You may also find that posting this within the PeopleSoft forum will get you a better response.

Hope that helps.