AS/400: Joining multiple files and getting multiple duplicates

Rsm425

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: March 29, 2014 4:29 AM Last updated: April 8, 2014 2:39 AM

Answer Wiki

Last updated: March 31, 2014 12:11 PM GMT

Michael Tidmarsh62,155 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,155 pts.
- ToddN200099,905 pts.

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

philpl1jb Mar 31, 2014 3:08 PM GMT

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 Apr 1, 2014 3:24 PM GMT

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 Apr 2, 2014 1:50 PM GMT

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.

99,905 pointsBadges:

report
philpl1jb Apr 2, 2014 2:35 PM GMT

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 Apr 8, 2014 2:39 AM GMT

Thanks everyone for the help. I will try each of the suggestions go from there!!

25 pointsBadges:

report