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.
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.
![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")