Skip to main content

ROWDIFF

ROWDIFF(left, right [, COUNT] )

leftThe left record, or a nested record structure.
rightThe right record, or a nested record structure.
COUNTOptional. 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