INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Check for multiple records.

Check for multiple records.

(OP)
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;

RE: Check for multiple records.

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

RE: Check for multiple records.

(OP)
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

RE: Check for multiple records.

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

Steve

"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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close