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     PLEASE help: MATCH one-to-many problem
Go
New
Search
Notify
Tools
Reply
  
PLEASE help: MATCH one-to-many problem
 Login/Join
 
<bigpgo>
posted
I'm trying to get the equivalent of a Left-Outer join in webfocus by doing MATCH with keyword "OLD". My 2 tables are A and B.

Table A: (ProjID = key):

ProjID ProjName ProjPhase
------ -------- ---------
1 bbq Phase1
2 bbq Phase1
3 bbq Phase2

Table BFrownerProjectName+ProjectPhase = key):

ProjectName ProjectPhase ProjInfo
----------- ------------ ---------
bbq Phase1 good
bbq Phase2 bad

Then I do:

MATCH FILE A
PRINT
ProjID
BY ProjName
BY ProjPhase
RUN

FILE B
PRINT
ProjectInfo
BY ProjectName AS ProjName
BY ProjectPhase AS ProjPhase
AFTER MATCH HOLD AS myReport OLD
END

TABLE FILE myReport ..
PRINT
ProjName ProjPhase ProjectInfo
..
END

This produces:
bbq Phase1 good
bbq Phase1 ''
bbq Phase2 bad

Note that it never finds a match for the 2nd record - the info is blank!!!
The match seems to assume it's a 1-to-1 relationship, and as soon as it finds a match in table B for the pair "bbq/Phase1", it stops trying to look for additional matches. Why??

I found this documentation online:

"If one set of sort fields is a subset of the other, then it�s a one-to-many merge (all that match.)OTHERWISE it�s a one-to-one".

Well, [ProjName, ProjPhase] is a subset of [ProjName, ProjPhase]. In fact, I tried throwing in some additional useless BY sort fields but I'm still getting 1-to-1. Any ideas? Thanks a lot.
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
Using MATCH FILE is not only a question of MATCH-Phrase and BY columns, it is also a question of using the right verbs. In TechSupport Knowledge Base: Fine Tuning MATCH Processing you can find more about all this.

Have fun ! Wink
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Big,
Use SUM, not Print, for the second part of the match.
However if you want to end up with records than there are currently in File A, then reverse the order of the match, and use HOLD NEW
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<bigpgo>
posted
Thank you - using SUM for the 2nd one did the trick!
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     PLEASE help: MATCH one-to-many problem

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.