AS/400 Query Join Duplicate Records

365 pts.
Tags:
AS/400 Duplicate Files
AS/400 Query
AS/400 Records
I have three files A, B, C in my join query. Test is between A - B, B - C. The type is 2 (match records to primary file). When I run the query, it is returning duplicates. What am I doing wrong?
ASKED: December 30, 2009  1:27 PM
UPDATED: December 31, 2009  3:52 PM

Answer Wiki

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

When you join two files and one or both of the files have multiple records for the join value, you will get multiple records in your output.
Example:
FIle A has 3 records. One with Key value of A and the other two with Key value of B,
File B has 5 records. Three records have the Key value of A and two records with the key value of B.
Here is the output:
FILE A – Key Value A with FILE B 1st Key Value of A
FILE A – Key Value A with FILE B 2nd Key Value of A
FILE A – Key Value A with FILE B 3rd Key Value of A
FILE A – 1sr Key Value B with FILE B 1st Key Value of B
FILE A – 1st Key Value B with FILE B 2nd Key Value of B
FILE A – 2nd Key Value of B with FILE B 1st Key Value of B
FILE A 2nd Key Value of B with FILE B 2nd Key Value of B
*
If you can include more elements in join, ot add Select/Omit Criteria, you may be able to reduce or eliminate the multiple records.
*
Else use SQL with SELECT DISTINCT

Discuss This Question: 2  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    CharlieBrowne has an appropriate example. To get some clear visual evidence with SQL, add a couple of additional fields -- RRN(A) and RRN(B) -- to the select list. The cause of duplicates should be clearer when you can see which records contribute the values. Tom
    125,585 pointsBadges:
    report
  • ElTerrifico
    When joining more than 2 files, I will often use multiple queries especially when duplicate records will be returned. For your example I would write 1 query to join A and B creating file D, then a second query joining D and C. With this exrta step, it is often easier to see where your duplicate records are coming from.
    620 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following