Wrkqry for join files

25 pts.
Tags:
JOIN statement
WRKQRY
Hi, can you help me how to join the two files but the two fields are not the same length e.g. 1st field (left side) is 9 (ex. A005B2589 job#) and the 2nd field (right side) is 11 (ex. A005B258901... the last two digit 01 is for partial#), kindly advise if it's possible?  Thanks and regards...

Answer Wiki

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

don’t include these fields in the join rules, if you have other join rules put them in or enter *all for the join rules. This will join every record in file 1 with file 2. Then use the select record to code FieldA = SUBSTR(FieldB,1,9) .. maybe you’ll have to create the 9 char field to use in the record select.
Phil

Discuss This Question: 6  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
  • Mario05
    thanks Philip... it's already work. One more thing if I had 3 files to be joined if its possible? Thanks again...
    25 pointsBadges:
    report
  • philpl1jb
    Yes - just add the third file and define how it's to be linked to the first or second. Phil
    49,940 pointsBadges:
    report
  • CarterC19
    Now, if these are two very big files, you may want to leave lots of run time for the process of joinging every record in file 1 with every record in file 2. Alternately, you could first define a query that extracts the data you want form the file with the 11-character file first, truncating the 11 character field to 9, putting all that out to a file then writing a second wuery that joins *that* file (which now has a 9-char field) to the original file 1 with the 9-character field.
    220 pointsBadges:
    report
  • Mario05
    Kindly check my query below but I've tried to run the result it mutiple or repeating with the same data, pls. assist... Thanks and regards 1) Join Files : Field Test Field *ALL 2) Define Result Fields: Field Expression INV#9 substr(ogtrno,1,9) 3) Select Records: AND/OR Field Test Value (Field, DQINVN EQ INV#9
    25 pointsBadges:
    report
  • philpl1jb
    please make a new question. Please provide more info on the files and how they are joined
    49,940 pointsBadges:
    report
  • TomLiotta
    ...I’ve tried to run the result it mutiple or repeating with the same data... JOINs often result in repeated data. It comes from the denormalization that is inherent in many joins. However, if we don't know the structure of the files and what data is repeating and why the repeats are a problem, we can't suggest any solutions. More information is needed. I'm not sure if it needs a new question, though. It does seem to follow somewhat naturally within the original question. But that might change based on what the actual problem turns out to be. Tom
    125,585 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