 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point HELP! Can't get all data with SET ALL = PASS
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point HELP! Can't get all data with SET ALL = PASS Read-Only TopicGo | Search | Notify | Tools |
| HELP! Can't get all data with SET ALL = PASS | Login/Join |
| Gold member |
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?  | ||
|
| Master |
MATCH FILE AAAAA ... ... BY xxxxx FILE BBBBB ... ... BY yyyyy AFTER MATCH HOLD OLD-OR-NEW END TABLE FILE HOLD PRINT ... END | |||
|
| Virtuoso |
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. | |||
|
| Virtuoso |
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>, | |||
|
| Gold member |
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.  | |||
|
| Gold member |
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... | |||
|
| Expert |
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. | |||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Silver Member |
try SET ALL=ON drew | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point HELP! Can't get all data with SET ALL = PASS
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point HELP! Can't get all data with SET ALL = PASSCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |