Solved

Data Comparison of Records in Same table

Posted on 2004-10-05
4
294 Views
Last Modified: 2013-12-26
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
Comment
Question by:dmcinally
4 Comments
 

Accepted Solution

by:
rchivers earned 125 total points
ID: 12255503
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
 
LVL 5

Assisted Solution

by:michaelstoffel
michaelstoffel earned 125 total points
ID: 12289664
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now