Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
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, 2004Reply With QuoteReport 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: 862 | Registered: May 24, 2004Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2004Reply With QuoteReport 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, 2004Reply With QuoteReport 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, 2003Reply With QuoteReport 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: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport 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, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
try

SET ALL=ON

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

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-2018 Information Builders, leaders in enterprise business intelligence.