ROWDIFF(left, right [, COUNT] )
| left | The left record, or a nested record structure. |
| right | The right record, or a nested record structure. |
| COUNT | Optional. Specifies returning a comma delimited set of zeros and ones (0,1) indicating which fields matched (0) and which did not (1). If omitted, a comma delimited set of the non-matching field names. |
| Return: | ROWDIFF returns a single value. |
The ROWDIFF function is valid for use only within a TRANSFORM structure for a JOIN operation and is used as the expression defining the output for a string field. Fields are matched by name and only like-named fields are included in the output.
Example:
FullName := RECORD
STRING30 forename;
STRING20 surname;
IFBLOCK(SELF.surname <> 'Windsor')
STRING20 middle;
END;
END;
in1rec := {UNSIGNED1 id,FullName name,UNSIGNED1 age,STRING5 title};
in2rec := {UNSIGNED1 id,FullName name,REAL4 age,BOOLEAN dead};
in1 := DATASET([{1,'Kevin','Halligan','',33,'Mr'},
{2,'Liz','Halligan','',33,'Dr'},
{3,'Elizabeth','Windsor',99,'Queen'}], in1rec);
in2 := DATASET([{1,'Kevin','Halligan','',33,false},
{2,'Liz','','Jean',33,false},
{3,'Elizabeth','Windsor',99.1,false}], in2rec);
outrec := RECORD
UNSIGNED1 id;
STRING35 diff1;
STRING35 diff2;
STRING35 diff3;
STRING35 diff4;
END;
outrec t1(in1 L, in2 R) := TRANSFORM
SELF.id := L.id;
SELF.diff1 := ROWDIFF(L,R);
SELF.diff2 := ROWDIFF(L.name, R.name);
SELF.diff3 := ROWDIFF(L, R, COUNT);
SELF.diff4 := ROWDIFF(L.name, R.name, COUNT);
END;
OUTPUT(JOIN(in1, in2, LEFT.id = RIGHT.id, t1(LEFT,RIGHT)));
// The result set from this code is:
//id diff1 diff2 diff3 diff4
//1 0,0,0,0,0 0,0,0
//2 name.surname,name.middle surname,middle 0,0,1,1,0 0,1,1
//3 age 0,0,0,0,1 0,0,0
See Also: TRANSFORM Structure, JOIN