COMBINE Form 2

Form 2 of COMBINE produces its result by passing each record from leftrecset, the group in the same ordinal position within rightrecset (along with the first record in the group) to the transform to produce a single output record. Grouping (if any) on the leftrecset is preserved. An error occurs if the number of records in the leftrecset differs from the number of groups in the rightrecset.

Example:

inrec := {UNSIGNED6 uid};
outrec := RECORD(inrec)
  STRING20 name;
  UNSIGNED score;
END;
nameRec := RECORD
  STRING20 name;
END;
 
resultRec := RECORD(inrec)
  DATASET(nameRec) names;
END;
ds := DATASET([1,2,3,4,5,6], inrec);
dsg := GROUP(ds, ROW);
i1 := DATASET([{1, 'Kevin'  ,10},
               {2, 'Richard', 5},
               {5, 'Nigel'  , 2},
               {0, ''       , 0} ], outrec);
i2 := DATASET([{1, 'Kevin Hall',      12},
               {2, 'Richard Chapman', 15},
               {3, 'Jake Smith',      20},
               {5, 'Nigel Hicks',    100},
               {0, ''          ,       0} ], outrec);
i3 := DATASET([ {1, 'Halligan', 8},
                {2, 'Richard',  8},
                {6, 'Pete',     4},
                {6, 'Peter',    8},
                {6, 'Petie',    1},
                {0, '',         0} ], outrec);
j1 := JOIN(dsg,i1,
           LEFT.uid = RIGHT.uid,
           TRANSFORM(outrec, 
                     SELF := LEFT; 
                     SELF := RIGHT),
           LEFT OUTER, MANY LOOKUP);
j2 := JOIN(dsg,i2, 
           LEFT.uid = RIGHT.uid,
           TRANSFORM(outrec, 
                     SELF := LEFT; 
                     SELF := RIGHT), 
           LEFT OUTER, MANY LOOKUP);
j3 := JOIN(dsg, i3, 
           LEFT.uid = RIGHT.uid,
           TRANSFORM(outrec, 
                     SELF := LEFT; 
                     SELF := RIGHT), 
           LEFT OUTER, MANY LOOKUP);
combined := REGROUP(j1, j2, j3);
resultRec t(inrec l,DATASET(RECORDOF(combined)) r) := TRANSFORM
  SELF.names := PROJECT(r, TRANSFORM(nameRec, SELF := LEFT));
  SELF := l; 
END;
res1 := COMBINE(dsg,combined,GROUP,t(LEFT, ROWS(RIGHT)(score != 0)),LOCAL);
OUPUT(res1);

//A variation using rows in a child query.
resultRec t2(inrec l, DATASET(RECORDOF(combined)) r) := TRANSFORM
  SELF.names := PROJECT(SORT(r, -score), 
                        TRANSFORM(nameRec, 
                                  SELF := LEFT));
                                  SELF := l;
END;
res2 := COMBINE(dsg,combined,GROUP,t2(LEFT,ROWS(RIGHT)(score != 0)),LOCAL);
OUTPUT(res2);

See Also: GROUP, REGROUP