September 30, 2004, 03:08 AM
<bigpgo>PLEASE help: MATCH one-to-many problem
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 B
ProjectName+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.
September 30, 2004, 12:22 PM
RolandUsing 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 !
September 30, 2004, 03:52 PM
susannahBig,
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
September 30, 2004, 09:18 PM
<bigpgo>Thank you - using SUM for the 2nd one did the trick!