Delete duplicate records.

[Jaytib]

RPGer trying SQL. I've read some of SQL posts in this forum but only see snippets of code and don't understand all of it. This is what I came up with just from using my SQL book and reading posts. I want to get detail for the hdr record cust/date. Then I want to delete dups and update the date if it doesnt match the hdr. Think I need another loop for checking for dup items? I have a cursor for all detail, and then should I add another cursor to check all the detail for dup items?
This is the subprocedure I am creating with a few comments. Any help will be appreciated. Have a great one!!

// Declare Cursor For customer price header and get the
// cust # with the most recent effective date

Exec Sql Declare hFile Cursor For
Select Distinct hCNBR, Max(hDATE)
From hFile
Group By hCNBR ;
Exec Sql Open hFile ;
Dow Loop ;
Exec Sql Fetch Next From hFile
Into :hCNBR, :hDATE ;
If SqlStt = SqlNoMoreData ;
Exec Sql Close hFile ;
Leave ;
Endif ;

2nd cursor
// select detail for the most recent pricing entry to get the effective date . May contain duplicate
// items per cust, so delete the dups and update the date for all items

Exec Sql Declare dFile Scroll Cursor For
Select *
From dFile
Where dCNBR = :hCNBR and dDATE >= :hFFDATE
Order by dDATE Desc
For Read Only ;
Exec Sql Open dFile ;
Exec Sql Fetch After From dFile ;
Exec Sql Fetch Prior From dFile
Into :rdFile ; (rcdfmt, file is data structure)
If SqlStt = SqlNoMoreData;
Exec Sql Close dFile;
Leave;
Endif;

// Now I have to check if item exists in prev entries and I am not sure what to do next.
//Then I have to delete dup items and update the date on others.

Exec Sql Delete From FileD
Where CNBR = :CNBR
and ITEM = :ITEM;
Exec Sql Update FileD
Set dDATE = :dDATE
Where ITEM <> :ITEM;

Enddo;

 

[Jaytib]

Please ignore this post. I moved it from RPG to Sql , sorry.