I was sure that using SET ALL = PASS and having JOIN KEY IN A TO ALL KEY IN B would give me all records from both tables if they match or not. After a lot of testing I found that it's only returning me records from the A table that aren't in the B table but not the opposite.

I can I get all records from both tables whenever they match or not???

I really need this to work, or I'm in troubles...

Is there an easy way to do that?

MATCH FILE AAAAA
PRINT
...
...
BY xxxxx
FILE BBBBB
PRINT
...
...
BY yyyyy
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT ...
END

quote:

Originally posted by TexasStingray:
[qb] MATCH FILE AAAAA
PRINT
...
...
BY xxxxx
FILE BBBBB
PRINT
...
...
BY yyyyy
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT ...
END [/qb]

Caution: MATCH FILE can be tricky. If there are multiple matches on the given sort keys, match file using PRINT can give unexpected results.

Similarly when the sort keys fields have differing names (BY XXXXX, BY YYYYY) - but that can be remedied using AS (BY XXXXX; BY YYYYY AS XXXXX) to rename the keys.

You can minimize the MATCH FILE processing (and avoid its complexities) by just getting the union of the keys, and joining back to the source file:


MATCH FILE file1
COUNT ENTRIES AS COUNT1
BY KEY1 AS KEY
RUN
FILE file2
COUNT ENTRIES AS COUNT2
BY KEY2 AS KEY
AFTER MATCH HOLD OLD-OR-NEW
END
JOIN CLEAR *
JOIN KEY IN HOLD TO KEY1 IN file1 AS J1
JOIN KEY IN HOLD TO KEY2 IN file2 AS J2
DEFINE FILE HOLD ...
END
TABLE FILE HOLD ...
Count1 or count2 =0 will indicate absense of matching record in file1 or 2.

If the matches on either side are not unique (if max.count1 or max.count2 > 1), use
JOIN ... TO ALL ...

This message has been edited. Last edited by: <Mabel>,

Interresting... I've never used match files. I will try that because both files have an identical sort field and there isn't more than one matching record. Thanks for the suggestion!

When I got Focus training, the teacher went very fast on the subject, saying that we should always avoid that technique, but it looks like I need that today.

That worked like a charm!!! You guys (and girls) are great

MATCH FILE BUDGEMON
SUM
BUDGET
BY
ZPROFCTR_KEY
RUN
FILE HLDACMON
SUM
NET_SALES_VALUE
CREDIT_VALUE
BY
ZPROFCTR_KEY
AFTER MATCH HOLD AS MONDATA OLD-OR-NEW
END
TABLE FILE MONDATA
PRINT *
END

I now have all records of both files with zeros on empty numeric fields, exactly what I wanted.

I'm sooooooo happy, I'll remember MATCH FILES...

OMG! "The teacher said you should avoid that subject". Good grief, Charlie Brown.
Moderator, please let Kristin Devlin know that.
FliPPeR, MATCH rocks.
fyi
JOIN thing TO ALL thing
is a 1 - to - many join
and
SET ALL=PASS means you're going to filter on stuff that exists only in the guest, not host, file. They're not the same at all.

WOW!!!
I feel excited to see your active participations and everyone tries to help each other. Please keep it going.
FliPPeR, I hope you find the suggestions helpful and enjoy the wonderful interactions with other people here.
Others, thanks to ALL of you for your inputs to this community. Please let me know if you have suggestions, comments, and/or new ideas about this forum. I'll be happy to hear feedbacks.

PS: A quick reminder, the Style Sheet Contest is still running. Submit your creation to Mabel by 02/05/2005, and each of the first prize winners will receive one of three Apple 20GB iPods!!

Regards,
Kerry
Focal Point Moderator
Email me: kerry_zhan@ibi.com

My problem is very similar, and maybe someone can help me too. Using mainframe Focus 7.3. I can't get an outer JOIN working with SET ALL=PASS, and it should work. My small test set of records includes a parent record without a child. Whether I JOIN A IN F1 TO B IN F2, or JOIN A IN F1 TO ALL B IN F2, it seems to me I should see that record that is in A. Nothing.
I have tried this with no filters on B and with filters on B. Either way, no parent.
Any ideas, please?

Thanks,
Phyllis

try

SET ALL=ON

drew