Check for multiple records. 1

[Jaytib]

I've read some posts in this forum but only see snippets of code and don't understand all of it. Somewhat new at this. This is the procedure I am creating with a few comments. Using the most recent cust date in hdr file, i created a cursor to retrieve data from detail file with the most recent date for each item for that customer. If the item is in detail more than once I want to delete it, and the most recent one i want to change the date to match the hdr date. I kinda got stuck on the best way to check all items for dups for the cust. I tried a loop but deleted more records than i wanted to. 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 >= :hDATE
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;

kinda stuck here
// 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;

 

[blom0344]

Would it not make more sense to use set-based logic to do this? Do the records have an unique identifier?

You can define a view against the table that stores the records / identifiers that qualify for most recent date per item/customer.

Flag the records (by update) for deletion that are not found in the view.. (based on the identifier / unique key hopefully)

Perform the actual deletes on the table by using the flag

Update the remaining records with the header date.

Example for the view select statement:

select * from detail_file df1 inner join
(select df2.customer as c,df2.item as i,max(df2.date) as d from detail_file df2 group by df2.customer,df2.item) temp
on df1.customer = temp.c and df1.item = temp.i and df1.date = temp.d

Performing the deletes is probably a 2-step action (deleting directly against the view may cause problems)
and you need a suitable field in the table to flag for delete

Ties Blom

 

[Jaytib]

Thanks, I took your advice, did a Join on both files and got the results I was looking for.
Thanks again, I appreciate all the help I can get.

 

[stevexff]

Give the man a star, then. It is the only way to show your appreciation...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]