Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check for multiple records. 1

Status
Not open for further replies.

Jaytib

Programmer
Sep 7, 2010
19
US
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;
 
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:

Code:
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

 
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. [smile]
 
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::perlDesignPatterns)[/small]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top