SQL question but concerns JDE data....

[pbare]

Hi All,

I hope someone will be able to help me with my query.

I need to write a SQL that will read data from the F08042 table for a certain data item. I need to keep the latest EFTO record and delete the other duplicate records

I have started with the following query
select jwan8, jwdtai, jwhstd, count(jwhstd)
from proddta.f08042
where jwfile = 'F060116'
and jwdtai = 'HMST'
having count(jwhstd) > 1
group by jwan8, jwdtai, jwhstd
order by jwan8

This returns me every Address number that has duplicate records for this data item.

I need to find the greatest EFTO record for each employee group and then delete any other records prior to that EFTO

ie.

AN8 DTAI HSTD EFTO
900085 HMST 3EC 06/24/2011 KEEP
900085 HMST 3EC 06/09/2011 DELETE
900085 HMST 3EC 07/29/2010 DELETE

Can this be done using SQL?
Any thoughts or suggestions would be appreciated.

Thanks
Pam

 

[Jamie_Minshull]

Hi,

I have done something similar on the F4111 when I needed to remove the duplicate records.

Have a look at the oracle dense_rank partition by function this should do the trick as you can rank the an8 on date and remove the oldest ones.

Jamie

 

[pbare]

Thanks for the suggestion... I will look into it.

I am not sure how to still delete the older records though...
Would you happen to have a sample of the query that you wrote?

Thanks
Pam

 

[Jamie_Minshull]

Hi,

Have a look at the sample below then replace the select for a delete, that should sort your issue.

I take it you are on Oracle?

Let me know how you get on.

Jamie

select * from (
select ildoco, illnid, ilukid, DENSE_RANK() over (partition by ildoco, illnid order by illnid, ildoco desc) RANKING
from proddta.f4111
where ildoco = '13590501'
)
where ranking <> 1