Eliminating Duplicate Data

[edbdqm]

I'm having problems eliminating duplicate data. I have two tables that creates a row of data each time the record is updated. So what I did was add two subqueries and added the max function to bring back the latest row of data. I also added the distinct keyword to bring back only one row of data also. I'm at a loss. Any help is definitely appreciated. See query below:

SELECT DISTINCT v.VET_SOCIAL_SECURITY_NUMBER,
V.VET_GIVEN_NAME,V.VET_MIDDLE_NAME,VET_FAMILY_NAME,
V.VET_DEATH_DATE, V.GENDER_TYPE_CODE, a.ADDRESS_LINE_1, a.ADDRESS_LINE_2,
a.ADDRESS_CITY_TEXT, a.STATE_CODE, a.ADDRESS_POSTAL_CODE_TEXT,
WS.WORKLOAD_STAT_TYPE_CODE, WS.WORKLOAD_STATUS_UPDATE_DATE,
wal.WORKLOAD_ACTIVITY_CODE,
ct.COMMS_TEMPLATE_FORM_NUMBER, cl.COMMS_LOG_MAILING_DATE
FROM VETERAN V, PATIENT P, WORKLOAD W, WORKLOAD_STATUS
WS,workload_activity_link wal,
address a, comms_log cl, comms_template ct
WHERE v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER AND V.VETERAN_IDENTIFIER
= P.VETERAN_IDENTIFIER AND
P.PATIENT_IDENTIFIER = W.PATIENT_IDENTIFIER AND
W.WORKLOAD_IDENTIFIER = WS.WORKLOAD_IDENTIFIER AND
ws.WORKLOAD_STATUS_IDENTIFIER = al.WORKLOAD_STATUS_IDENTIFIER and
w.WORKLOAD_IDENTIFIER = cl.WORKLOAD_IDENTIFIER and
cl.COMMS_TEMPLATE_IDENTIFIER = ct.COMMS_TEMPLATE_IDENTIFIER and
ws.WORKLOAD_STATUS_UPDATE_DATE = (SELECT MAX (WS.WORKLOAD_STATUS_UPDATE_DATE)
FROM WORKLOAD_STATUS WS
WHERE WS.WORKLOAD_STATUS_IDENTIFIER= WAL.WORKLOAD_STATUS_IDENTIFIER) and
v.VET_DEATH_DATE is null and
ct.COMMS_TEMPLATE_FORM_NUMBER = '742-200' and
ws.WORKLOAD_STATUS_UPDATE_DATE between '01-MAR-04' and '01-SEP-04'
AND WS.WORKLOAD_STAT_TYPE_CODE = '04' AND
wal.WORKLOAD_ACTIVITY_CODE = any ('20', '21') and
v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER and
a.ADDRESS_START_DATE = (SELECT MAX (A.ADDRESS_START_DATE)
from address a
where v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER) and
rownum <1100

[balajiyes]

Try this:
SELECT v.VET_SOCIAL_SECURITY_NUMBER,
V.VET_GIVEN_NAME,V.VET_MIDDLE_NAME,VET_FAMILY_NAME,
V.VET_DEATH_DATE, V.GENDER_TYPE_CODE, a.ADDRESS_LINE_1, a.ADDRESS_LINE_2,
a.ADDRESS_CITY_TEXT, a.STATE_CODE, a.ADDRESS_POSTAL_CODE_TEXT,
WS.WORKLOAD_STAT_TYPE_CODE, WS.WORKLOAD_STATUS_UPDATE_DATE,
wal.WORKLOAD_ACTIVITY_CODE,
ct.COMMS_TEMPLATE_FORM_NUMBER, cl.COMMS_LOG_MAILING_DATE
FROM VETERAN V, PATIENT P, WORKLOAD W, WORKLOAD_STATUS
WS,workload_activity_link wal,
address a, comms_log cl, comms_template ct,
(SELECT WS.WORKLOAD_STATUS_IDENTIFIER, MAX (WS.WORKLOAD_STATUS_UPDATE_DATE) WORKLOAD_STATUS_UPDATE_DATE
FROM WORKLOAD_STATUS WS group by WS.WORKLOAD_STATUS_IDENTIFIER) iws,
(SELECT a.VETERAN_IDENTIFIER, MAX (A.ADDRESS_START_DATE) ADDRESS_START_DATE from address a
group by a.VETERAN_IDENTIFIER) iad
WHERE v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER
AND V.VETERAN_IDENTIFIER = P.VETERAN_IDENTIFIER
AND P.PATIENT_IDENTIFIER = W.PATIENT_IDENTIFIER
AND W.WORKLOAD_IDENTIFIER = WS.WORKLOAD_IDENTIFIER
AND ws.WORKLOAD_STATUS_IDENTIFIER = al.WORKLOAD_STATUS_IDENTIFIER
and w.WORKLOAD_IDENTIFIER = cl.WORKLOAD_IDENTIFIER
and cl.COMMS_TEMPLATE_IDENTIFIER = ct.COMMS_TEMPLATE_IDENTIFIER
and ws.WORKLOAD_STATUS_UPDATE_DATE = iws.WORKLOAD_STATUS_UPDATE_DATE
and v.VET_DEATH_DATE is null
and ct.COMMS_TEMPLATE_FORM_NUMBER = '742-200'
and ws.WORKLOAD_STATUS_UPDATE_DATE between '01-MAR-04' and '01-SEP-04'
AND WS.WORKLOAD_STAT_TYPE_CODE = '04'
AND wal.WORKLOAD_ACTIVITY_CODE = any ('20', '21')
and v.VETERAN_IDENTIFIER = a.VETERAN_IDENTIFIER
and a.ADDRESS_START_DATE = iad.ADDRESS_START_DATE
and rownum <1100