The following jointypes produce the following types of results, based on the records matching produced by the joincondition:
INNER | This is the default if no jointype is specified. Only those records that exist in all datasets in the setofdatasets. |
LEFT OUTER | At least one record for every record in the first dataset in the setofdatasets. |
LEFT ONLY | One record for every record in the first dataset in the setofdatasets for which there is no match in any of the subsequent datasets. |
MOFN(min [,max]) | One record for every record with matching records in min number of adjacent datasets within the setofdatasets. If max is specified, the record is not included if max number of dataset matches are exceeded. |
Example:
Rec := RECORD,MAXLENGTH(4096) STRING1 Letter; UNSIGNED1 DS; UNSIGNED1 Matches := 0; UNSIGNED1 LastMatch := 0; SET OF UNSIGNED1 MatchDSs := []; END; ds1 := DATASET([{'A',1},{'B',1},{'C',1},{'D',1},{'E',1}],Rec); ds2 := DATASET([{'A',2},{'B',2},{'H',2},{'I',2},{'J',2}],Rec); ds3 := DATASET([{'B',3},{'C',3},{'M',3},{'N',3},{'O',3}],Rec); ds4 := DATASET([{'A',4},{'B',4},{'R',4},{'S',4},{'T',4}],Rec); ds5 := DATASET([{'B',5},{'V',5},{'W',5},{'X',5},{'Y',5}],Rec); SetDS := [ds1,ds2,ds3,ds4,ds5]; Rec XF(Rec L,DATASET(Rec) Matches) := TRANSFORM SELF.Matches := COUNT(Matches); SELF.LastMatch := MAX(Matches,DS); SELF.MatchDSs := SET(Matches,DS); SELF := L; END; j1 := JOIN(SetDS, STEPPED(LEFT.Letter=RIGHT.Letter), XF(LEFT,ROWS(LEFT)),SORTED(Letter)); j2 := JOIN(SetDS, STEPPED(LEFT.Letter=RIGHT.Letter), XF(LEFT,ROWS(LEFT)),SORTED(Letter),LEFT OUTER); j3 := JOIN(SetDS, STEPPED(LEFT.Letter=RIGHT.Letter), XF(LEFT,ROWS(LEFT)),SORTED(Letter),LEFT ONLY); j4 := JOIN(SetDS, STEPPED(LEFT.Letter=RIGHT.Letter), XF(LEFT,ROWS(LEFT)),SORTED(Letter),MOFN(3)); j5 := JOIN(SetDS, STEPPED(LEFT.Letter=RIGHT.Letter), XF(LEFT,ROWS(LEFT)),SORTED(Letter),MOFN(3,4)); OUTPUT(j1); OUTPUT(j2); OUTPUT(j3); OUTPUT(j4); OUTPUT(j5);
See Also: TRANSFORM Structure, RECORD Structure, SKIP, ROWDIFF, STEPPED, KEYED/WILD, MERGEJOIN