AS/400: Joining multiple files and getting multiple duplicates

25 pts.
Tags:
AS/400
AS/400 Duplicate Files
I am a novice AS/400 user and have been thrown into the lions den. I need to pull info from 4 files that have at least 1 common field. Regardless of using 1 or multiple tests I get a lot of duplicates. There has to be a way to not get them. Our former and now deceased AS/400 guru did queries all of the time with no problem. Any help would be appreciated. I'm going bald trying to figure it out!!
Asked: Last updated:
Related Questions
1

Answer Wiki

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

You most likely need to link your files on a more unique key. This will eliminate the duplicates. Check for similar fields it the other files and join on those as well. An example would be say an order header file and detail file may contain a filed like order level. If you just join on order number you will pick up all order levels in the detail when lookin for order 1 level 1. When when the next order level is read from the header, order 1 level 2  you will pick up ALL the same data from the order detail. So in this example,  join on order number and order level to eliminate duplicates.

Discuss This Question: 5  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.
  • philpl1jb
    If you show us your SQL, a sample of input data and a sample of the desired or wrong results we can probably help.
    54,090 pointsBadges:
    report
  • Rsm425
     Field             Test     Field   
     UDFBKEY1          EQ       ICITEM  
     UDFBKEY1          EQ       IFITEM  
     UDFBKEY1          EQ       J6ITEM

    Unfortunatly there is only one field in the primary that has the same data as the other 3 files.  It is only making one duplicate now and I can just have excel remove them. :(  
    25 pointsBadges:
    report
  • ToddN2000
    I'd pick one of the duplicate items and see if there are multiple instances of it in each of the 4 files individually. That may point you to the problem file where you may need to refine your join and or select to get just single occurrences.
    135,305 pointsBadges:
    report
  • philpl1jb

    Looks like you are using Query/400 (WrkQry)

    The Sort and Summary functions can be used to eliminate duplicates.

    Sometimes it's necessary to make a series of queries .. each creating an output file which is an input to the next process.

    54,090 pointsBadges:
    report
  • Rsm425
    Thanks everyone for the help. I will try each of the suggestions go from there!!
    25 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: