Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Comparison of Records in Same table

Posted on 2004-10-05
4
Medium Priority
?
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Accepted Solution

by:
rchivers earned 500 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses

721 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