Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Compare 2 Tables (11g)
Oracle Compare 2 Tables [message #608276] Tue, 18 February 2014 05:47 Go to next message
developer12
Messages: 88
Registered: July 2013
Member
I have 2 tables defined as :

Table A_TXN
CREATE TABLE A_TXN
(
  T_ID         NUMBER,
  ACC          VARCHAR2(8),
  T_AMT  	   NUMBER,
  UPDATE_DATE      DATE
);


Table B_TXN
CREATE TABLE A_TXN
(
  T_ID         	   NUMBER,
  ACCOUNT          VARCHAR2(8),
  COST  	   	   NUMBER,
  UPDATE_DATE      DATE
);


Now I need to compare both the tables based on inner join to A_TXN.ACC=B_TXN.ACCOUNT and A_TXN.UPDATE_DATE=B_TXN.UPDATE_DATE
The results to be obtained are as:

ACCOUNT T_ID(A_TXN) T_ID(B-TXN) Diff T_AMT(A_TXN) COST(B_TXN) 12345 111 111 0 9087 9089

Diff
2

Here in the results:
Account is the account number i.e., ACC(TXN_A) or ACCOUNT(TXN_B)
T_ID is the COUNT of the t_id's on that particular date.
Diff is the diff between the 2 values.
T_AMT and COST is the same as T_ID logic, such that T_AMT and COST are rolled up to give the SUM of the values for the particular ACC and particular date.
Can anyone help me how i can go ahead with this??
Re: Oracle Compare 2 Tables [message #608278 is a reply to message #608276] Tue, 18 February 2014 05:54 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This will give you the different rows:
select 'TABLE 1', t.*
from ( select t1.* from mytab1 t1
       minus
       select t2.* from mytab2 t2 ) t
union all
select 'TABLE 2', t.*
from ( select t2.* from mytab2 t2
       minus
       select t1.* from mytab1 t1 ) t
/

You can then display it as you want.



Previous Topic: not in, not exists query
Next Topic: First Character in "numeric-Alpha"
Goto Forum:
  


Current Time: Mon Sep 01 17:30:56 CDT 2014

Total time taken to generate the page: 0.14054 seconds