Hi,
I have a table with 50 columns, lets call it Table_A . There is a identical table called Table_B. The data in Table_B gets flushed every night and data from Table_A is moved to Table_B. A new set of data is inserted into Table_A. Now I need to find out which field value is changed and what is changed . A minus command can give the changed records, but how to get the what was prior value for each changed field . Here is what I am looking for
WITH TABLE_A AS ( SELECT 1 ID, 'JOHN' NAME, 'SALES' DIV FROM DUAL
UNION
SELECT 2 ID, 'MARRY' NAME, 'ACCOUNTS' DIV FROM DUAL
UNION
SELECT 3 ID, 'KIM' NAME, 'SERVICE' DIV FROM DUAL),
TABLE_B AS ( SELECT 1 ID, 'JOHN' NAME, 'SALES' DIV FROM DUAL
UNION
SELECT 2 ID, 'MARRY' NAME, 'ACCOUNTS' DIV FROM DUAL
UNION
SELECT 3 ID, 'KIM' NAME, 'SALES' DIV FROM DUAL)
SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B
what i want is something that will spit out this (only one record, because there is only one diff record found in the above query.)
ID, OLD_ID, NAME, OLD_NAME , DIV , OLD_DIV
-- ------ ---- -------- --- -------
3 3 KIM KIM SERVICE SALES
The above one is for sample purpose, The actual table has 50 fields, and except the ID field, all other field values might change.
All ideas and solutions are appreciated.
Thanks in advance.