Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     HELP! Can't get all data with SET ALL = PASS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
HELP! Can't get all data with SET ALL = PASS
 Login/Join
 
Gold member
posted
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? Confused
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Master
posted Hide Post
MATCH FILE AAAAA
PRINT
...
...
BY xxxxx
FILE BBBBB
PRINT
...
...
BY yyyyy
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT ...
END
 
Posts: 865 | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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>,
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
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. Wink
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Gold member
posted Hide Post
That worked like a charm!!! You guys (and girls) are great Smiler

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...
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Report This Post
Expert
posted Hide Post
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.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
WOW!!!
I feel excited to see your active participations and everyone tries to help each other. Big Grin 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!! Wink

Regards,
Kerry Smiler
Focal Point Moderator
Email me: kerry_zhan@ibi.com
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Member
posted Hide Post
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
 
Posts: 15 | Registered: May 17, 2004Report This Post
Silver Member
posted Hide Post
try

SET ALL=ON

drew
 
Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     HELP! Can't get all data with SET ALL = PASS

Copyright © 1996-2020 Information Builders