Removing duplicate records using WRKQRY

2460 pts.
Tags:
AS 400
Hi, I have a file with some duplicate records and I want to filter them using wrkqry can anyone please advise how to remove duplicate records using this? I am comparing three fields lets say F1,F2,F3 and comparing them with each other for similar records and for field 4 lets say it's f4 i want to compare it like where f1,f2 and f3 should be equal records and records for f4 field should not be same this way i want to filter records using wrkqry any idea/advise/suggestion from experts please? Thanks!


Software/Hardware used:
AS400
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

With WRKQRY you wont be able to remove the records from the file. You can filter the view by sorting and summarizing the data. The only way to get a version of the file without those would be to direct your output from the WRKQRY command to a new file and use that one. You could write a SQL statement to drop those record and run it using the STRSQL command.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • 6r
    Any updates by experts please?
    2,460 pointsBadges:
    report
  • ToddN2000
    If you are still having problems, cut and paste you select records definition and we will take a look at what may be wrong. A lot of time if you have an OR condition it can break your select as it basically takes everything defined after it as a completely new set of conditions.
    131,550 pointsBadges:
    report
  • ToddN2000
    Better yet, if you have access to it, use the command WRKQMQRY.
    It's much more flexible than WRKQRY and closer to SQL.
    131,550 pointsBadges:
    report
  • GregManzo
    Try this in SQL:

    delete from FILE c 
    where (F1,F2,F3) in (select F1,F2,F3 from FILE a group by F1,F2,F3 having count(*)>1)
    and rrn(c) > 
    (select min(rrn(b)) from FILE b 
    where (F1,F2,F3) in (select F1,F2,F3 from FILE a group by F1,F2,F3 having count(*)>1))

    Replace FILE with your file name.
    I make no promises about performance and I'm assuming the first duplicate (the one to keep) is the lowest rrn.
    2,950 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: