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.
Important: Form 2 also uses ordinal pairing, at group granularity. Each LEFT record is paired with the RIGHT group in the same ordinal position. If the LEFT record count and RIGHT group count do not match, COMBINE fails.
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);
OUTPUT(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);