Skip to main content

COMBINE

COMBINE( leftrecset, rightrecset [, transform ][,LOCAL])

COMBINE( leftrecset, rightrecset, GROUP , transform [,LOCAL] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

leftrecsetThe LEFT record set.
rightrecsetThe RIGHT record set.
transformThe TRANSFORM function call. If omitted, COMBINE returns all fields from both the leftrecset and rightrecset, with the second of any duplicate named fields removed.
LOCALThe LOCAL option is required when COMBINE is used on Thor (and implicit in hThor/Roxie).
GROUPSpecifies the rightrecset has been GROUPed. If this is not the case, an error occurs.
UNORDEREDOptional. Specifies the output record order is not significant.
ORDEREDSpecifies the significance of the output record order.
boolWhen False, specifies the output record order is not significant. When True, specifies the default output record order.
STABLEOptional. Specifies the input record order is significant.
UNSTABLEOptional. Specifies the input record order is not significant.
PARALLELOptional. Try to evaluate this activity in parallel.
numthreadsOptional. Try to evaluate this activity using numthreads threads.
ALGORITHMOptional. Override the algorithm used for this activity.
nameThe algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options.
Return:COMBINE returns a record set.

The COMBINE function combines leftrecset and rightrecset on a record-by-record basis in the order in which they appear in each.

COMBINE TRANSFORM Function Requirements

For form 1, the transform function must take at least two parameters: a LEFT record which must be in the same format as the leftrecset and a RIGHT record which must be in the same format as the rightrecset. The format of the resulting record set may be different from the inputs.

For form 2, the transform function must take at least three parameters: a LEFT record which must be in the same format as the leftrecset, a RIGHT record which must be in the same format as the rightrecset, and a ROWS(RIGHT) whose format must be a DATASET(RECORDOF(rightrecset)) parameter. The format of the resulting record set may be different from the inputs.

COMBINE Form 1

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

Example:

inrec := RECORD
  UNSIGNED6 uid;
END;
outrec := RECORD(inrec)
  STRING20  name;
  STRING11  ssn;
  UNSIGNED8 dob;
END;
ds := DATASET([1,2,3,4,5,6], inrec);
i1 := DATASET([ {1, 'Kevin'  }, 
                {2, 'Richard'}, 
                {5, 'Nigel'  }], 
              {UNSIGNED6 uid, STRING10 name });
i2 := DATASET([ {3, '000-12-3462'}, 
                {5, '000-12-8723'}, 
                {6, '000-10-1002'}], 
              {UNSIGNED6 uid, STRING11 ssn });
i3 := DATASET([ {1, 19700117}, 
                {4, 19831212}, 
                {6, 20010101}], 
              {UNSIGNED6 uid, UNSIGNED8 dob}); 
j1 := JOIN(ds, i1, LEFT.uid = RIGHT.uid, LEFT OUTER, LOOKUP);
j2 := JOIN(ds, i2, LEFT.uid = RIGHT.uid, LEFT OUTER, LOOKUP);
j3 := JOIN(ds, i3, LEFT.uid = RIGHT.uid, LEFT OUTER, LOOKUP);
combined1 := COMBINE(j1, j2, 
                     TRANSFORM(outRec, 
                               SELF := LEFT;
                               SELF := RIGHT;
                               SELF := []),
                     LOCAL);
combined2 := COMBINE(combined1, j3, 
                     TRANSFORM(outRec,
                               SELF.dob := RIGHT.dob;
                               SELF := LEFT),
                     LOCAL);
OUTPUT(combined1);
OUTPUT(combined2);

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