Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Data Comparison of Records in Same table

Hello, This is a tough one for me anyway. I have a set of tables, and in the tables I have versioned data - e.g. I create a complete copy of the set of data and increment the version number. I then make modifications to some of the fields. I would like to be able to compare one version to another version. In the case where there is no data on the earlier version - I would like to display 'N/A', in the case where there is data in the earlier version and not the more recent version - I would like to display 'DELETED', in the case where there is data in both - I would like to display the column name, previous value and current value.

Thanks for the help
DAvid
0
dmcinally
Asked:
dmcinally
2 Solutions
 
rchiversCommented:
not sure of a couple of things like what your key columns are to link the tables but you could do something like this.
on a window create two invisible datawindows dw1, dw2
use the same dataobject on both of them.
store the data and column names in arrays. Loop through them doing the comparison
this script pops up a message box if the values are different, you could store them in a table or whatever you want.

any la_data1[], la_data2[]
string ls_col [], ls_string
integer li_count, i
//retrieve the two datawindows based on your key values

dw1.retrieve ()
dw2.retrieve ()
// find out how many columns are in the datawindow
li_count = integer(dw1.Object.DataWindow.Column.Count)
// load column names in string array
for i = 1 to li_count
      ls_string = '#' + string (i) + '.name'
      ls_col [i] = dw1.describe (ls_string)
next

//store the values from datawindows in the arrays
la_data1 = dw1.object.data [1]  // store values from dw1 row 1 in array 1
la_data2 = dw2.object.data [1]  // store values from dw2 row 1 in array 2

// check to see if both datawindows have the same number of columns
if upperbound (la_data1) <> upperbound (la_data2) then
      Messagebox ("Error","Column count mismatch")
      return
end if
for i = 1 to upperbound (la_data1)
      if la_data1 [i] <> la_data2 [i] then
           messagebox ("Data Difference Found","Column " + ls_col [i] + '~n~r' +&
                                                                 'Value 1 = ' + string (la_data1[i]) + '~n~r' +&
                                                                 'Value 2 = '  + string (la_data2[i]))
      end if
next
0
 
michaelstoffelCommented:
I'd do the comparison in SQL since it is better suited for set based operations.  You don't specify a database, so I'll assume SQLServer 2000.  This is not tested and it's been awhile so the syntax is probably off, but you'll get the idea

Table( version, key, field)

SELECT CHOOSE CASE newer.key IS NULL THEN 'Deleted'
                          CASE older.key IS NULL THEN 'Inserted'
                          ELSE 'Modified'
            END CHOOSE AS Action,
            older.field AS OldValue,
            newer.field AS NewValue
FROM Table newer
FULL OUTER JOIN Table older
   ON newer.key = older.key
WHERE newer.version = :newVersionArg
  AND older.version = :oldVersionArg

Make a datawindow based on the above SQL and pass the version args
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now