Home » SQL & PL/SQL » SQL & PL/SQL » Compare Data (10g)
Compare Data [message #294850] Sun, 20 January 2008 03:04 Go to next message
ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore
Junior Member
Hi,

I process a set of data (lets say 1M records) and take the back up of that data. Again I run process and then want to compare the data processed with the first set of data. How can I compare the second set data with the first set? I have more than 70 columns in the table and cannot use a cursor to compare the values in each and every column. Is there any way to do this in a query?

Thanks,
Ravi
Re: Compare Data [message #294851 is a reply to message #294850] Sun, 20 January 2008 03:25 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
You need to be a bit more specific.
What do you mean by COMPARE?

There are so many ways to do this
If it's just checking for the existence of data sets use MINUS.

You can also think of some kind of flag variable to record a successful process run. The whole idea of running the process just for validation(I'm assuming that's what you're doing) is rework
Re: Compare Data [message #294863 is a reply to message #294851] Sun, 20 January 2008 04:44 Go to previous messageGo to next message
ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore
Junior Member
Thanks for the reply.

Compare data means - Compare the data in table xyz_backup against xyz table.

Why we are doing this is - Before implementing a new logic, we need to run the existing logic and take the back up of the processed data in xyz_backup table and implement the new logic and run the process. Then check the data in xyz table against the data processed previously.

We can do it by writing to flat files and comparing them. But I want know is there any function or any query which actually does this.

Thanks,
Ravi
Re: Compare Data [message #294865 is a reply to message #294863] Sun, 20 January 2008 04:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
You need to be a bit more specific.
What do you mean by COMPARE?
You haven't explained fully.

What do you want to compare?
Each and every column?
Do you want to get the rows, which are having difference values in specific columns?
Try whether you can achieve that using MINUS, as mentioned in the above post.
select col1,col2,col3 from xyz
minus
select col1,col2,col3 from xyz_backup;
By
Vamsi
Re: Compare Data [message #294892 is a reply to message #294865] Sun, 20 January 2008 08:34 Go to previous messageGo to next message
ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore
Junior Member
I want to compare each and every column in a row (except primary key col which is seq generator) in xyz table with corresponding row in xyz_backup table.

Example: Lets say, I'm processing customer data and in the first run it got processed and the customer_chrg_amt got calculated as Rs. 1000. After implementing the new logic, each and every column values matches the previous run, but the new logic is somehow not calculating the customer_chrg_amt properly and hence its populated as Rs. 500. And its not repeating for all customers. So we need to identify those customers.

With this, I want get the records which are having difference values in the columns. In the above ex, I want to get the customer_no whose charge amount got calculated incorrectly.

Hope I made it clear.

Thanks,
Ravi
Re: Compare Data [message #294894 is a reply to message #294892] Sun, 20 January 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at these articles:
On Injecting and Comparing
How to compare two tables of data?

Regards
Michel

Re: Compare Data [message #294900 is a reply to message #294894] Sun, 20 January 2008 11:55 Go to previous message
ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore
Junior Member
Thanks Mike. This works for me.

Thanks,
Ravi
Previous Topic: HELP -> DBMS_LOG.FILEEXISTS
Next Topic: sequence
Goto Forum:
  


Current Time: Fri Dec 02 23:08:03 CST 2016

Total time taken to generate the page: 0.10899 seconds