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

Delete duplicate records.

Delete duplicate records.

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

RE: Delete duplicate records.

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

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