comparing Multiple rows

[davidvilla]

Hi all,

I can easily compare two rows of a table and get the fields and values that are different. Can I compare multiple rows the same way?

Example:

field1 field2 field3 field4
one abc 123 bell
one def 123 bell
one abc 456 bell
two def 456 bell

My result should say:
If I compare all the 4 rows
record1: field1=one,field2=abc,field3=123
record2: field1=one,field2=def,field3=123
record3: field1=one,field2=abc,field3=456
record4: field1=two,field2=def,field3=456

If I compare first 3 rows
record1: field2=abc,field3=123
record2: field2=def,field3=123
record3: field2=abc,field3=456

If I compare first 2 rows
record1: field2=abc
record2: field2=def

The number of rows to compare is dynamic. The result should list all the fields and values that are different in atleast one of the compared records.

I did compare any two rows using:

DOi = 1 TO hRec1Buffer:NUM-FIELDS:
iF hRec1Buffer:BUFFER-FIELD(i):BUFFER-VALUE <> hRec2Buffer:BUFFER-FIELD(i):BUFFER-VALUE THEN
ASSIGN
cRec1Diff = cRec1Diff + (iF cRec1Diff = '':U THEN '':U ELSE ',':U ) + hRec1Buffer:BUFFER-FIELD(i):NAME + "=" + QUOTER(hRec1Buffer:BUFFER-FIELD(i):BUFFER-VALUE,'"')
cRec2Diff = cRec2Diff + (iF cRec2Diff = '':U THEN '':U ELSE ',':U ) + hRec2Buffer:BUFFER-FIELD(i):NAME + "=" + QUOTER(hRec2Buffer:BUFFER-FIELD(i):BUFFER-VALUE, '"').
END.

 

[Stefan]

Here you go.

DEFINE TEMP-TABLE tt NO-UNDO
   FIELD f1 AS CHARACTER
   FIELD f2 AS CHARACTER
   FIELD f3 AS CHARACTER
   FIELD f4 AS CHARACTER
   .


CREATE tt. ASSIGN tt.f1 = "one" tt.f2 = "abc" tt.f3 = "123" tt.f4 = "bell".
CREATE tt. ASSIGN tt.f1 = "one" tt.f2 = "def" tt.f3 = "123" tt.f4 = "bell".
CREATE tt. ASSIGN tt.f1 = "one" tt.f2 = "abc" tt.f3 = "456" tt.f4 = "bell".
CREATE tt. ASSIGN tt.f1 = "two" tt.f2 = "def" tt.f3 = "456" tt.f4 = "bell".  


DEFINE VARIABLE hq      AS HANDLE      NO-UNDO.
DEFINE VARIABLE hb1     AS HANDLE      NO-UNDO.
DEFINE VARIABLE hb2     AS HANDLE      NO-UNDO.


DEFINE VARIABLE ii      AS INTEGER     NO-UNDO.
DEFINE VARIABLE lfirst  AS LOGICAL     NO-UNDO.
DEFINE VARIABLE irow    AS INTEGER     NO-UNDO.
DEFINE VARIABLE ldiff   AS LOGICAL     NO-UNDO EXTENT.
DEFINE VARIABLE cdiff   AS CHARACTER   NO-UNDO.




CREATE BUFFER hb1 FOR TABLE "tt" BUFFER-NAME "tt1".
CREATE BUFFER hb2 FOR TABLE "tt" BUFFER-NAME "tt2".


CREATE QUERY hq.
hq:SET-BUFFERS( hb1, hb2 ).
hq:QUERY-PREPARE( "FOR EACH tt1, EACH tt2":U ).




EXTENT(ldiff) = hb1:NUM-FIELDS.


DO ii = 1 TO hb1:NUM-FIELDS:
   hq:QUERY-OPEN().
   DO WHILE hq:GET-NEXT() AND NOT ldiff[ii]:
      ldiff[ii] = hb1:BUFFER-FIELD(ii):BUFFER-VALUE <> hb2:BUFFER-FIELD(ii):BUFFER-VALUE .
   END.
   hq:QUERY-CLOSE().
END.




hq:SET-BUFFERS( hb1 ).
hq:QUERY-PREPARE( "FOR EACH tt1" ).
hq:QUERY-OPEN().


DO WHILE hq:GET-NEXT():
   ASSIGN
      irow = irow + 1
      lfirst = TRUE
      .
   DO ii = 1 TO hb1:NUM-FIELDS:
      IF ldiff[ii] THEN DO:
         IF lfirst THEN
            ASSIGN
               cdiff = cdiff + SUBSTITUTE( "~nrecord=&1: ", irow )
               lfirst = FALSE
               .
         cdiff = cdiff + SUBSTITUTE( "&1=&2, ":U, hb1:BUFFER-FIELD(ii):NAME, QUOTER( hb1:BUFFER-FIELD(ii):BUFFER-VALUE ) ).
      END.
   END.
END.


DELETE OBJECT hq.
DELETE OBJECT hb2.
DELETE OBJECT hb1.


MESSAGE SUBSTRING( cdiff, 2 ) VIEW-AS ALERT-BOX.