Solved

Data Comparison of Records in Same table

Posted on 2004-10-05
4
289 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
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: …
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

706 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

15 Experts available now in Live!

Get 1:1 Help Now