| Oracle Compare 2 Tables [message #608276] |
Tue, 18 February 2014 05:47  |
 |
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  |
 |
Michel Cadot
Messages: 68645
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
