Thank You For Help.. Shared some sample script..
Abhi, April 07, 2017 - 7:24 am UTC
Hi Tom,
Thank you for your reply.
Please find below script
create table Mig_Tab_Compare
( iSRNo NUMBER,
szTableKey VARCHAR2(10),
szSourceTableName VARCHAR (200),
szSourceColumnName VARCHAR (50),
szDestinationTableName VARCHAR (200),
szDestinationColumnName VARCHAR (50),
szWhereClause VARCHAR2(1000),
cStatus CHAR(1)
) ;
insert into Mig_Tab_Compare (ISRNO, SZTABLEKEY, SZSOURCETABLENAME, SZSOURCECOLUMNNAME, SZDESTINATIONTABLENAME, SZDESTINATIONCOLUMNNAME, SZWHERECLAUSE, CSTATUS)
values (1, 'CUSTOMER', 'MIG_TRN_CUSTOMER', 'SZCUSTOMERNO', 'COL_MST_CUSTOMER', 'SZLEGACYCUSTOMERNO', 'SZCUSTOMERNO=SZLEGACYCUSTOMERNO', 'A');
insert into Mig_Tab_Compare (ISRNO, SZTABLEKEY, SZSOURCETABLENAME, SZSOURCECOLUMNNAME, SZDESTINATIONTABLENAME, SZDESTINATIONCOLUMNNAME, SZWHERECLAUSE, CSTATUS)
values (2, 'CUSTOMER', 'MIG_TRN_CUSTOMER', 'SZNAME', 'COL_MST_CUSTOMER', 'SZNAME', null, 'A');
insert into Mig_Tab_Compare (ISRNO, SZTABLEKEY, SZSOURCETABLENAME, SZSOURCECOLUMNNAME, SZDESTINATIONTABLENAME, SZDESTINATIONCOLUMNNAME, SZWHERECLAUSE, CSTATUS)
values (3, 'SERVICES', 'MIG_TRN_SERVICEINSTANCE', 'SZBILLABLEACCOUNTID', 'COL_TRN_AGREEMENT,COL_TRN_SERVICEINSTANCE', 'SZLEGACYAGREEMENTNO', 'MIG_TRN_SERVICEINSTANCE.SZCONTRACTID=COL_TRN_SERVICEINSTANCE.SZCONTRACTID AND COL_TRN_AGREEMENT.IAGREEMENTSEQNO=COL_TRN_SERVICEINSTANCE.IAGREEMENTSEQNO
AND COL_TRN_AGREEMENT.SZLEGACYAGREEMENTNO=MIG_TRN_SERVICEINSTANCE.SZBILLABLEACCOUNTID', 'A');
insert into Mig_Tab_Compare (ISRNO, SZTABLEKEY, SZSOURCETABLENAME, SZSOURCECOLUMNNAME, SZDESTINATIONTABLENAME, SZDESTINATIONCOLUMNNAME, SZWHERECLAUSE, CSTATUS)
values (4, 'SERVICES', 'MIG_TRN_SERVICEINSTANCE', 'SZCONTRACTID', 'COL_TRN_AGREEMENT,COL_TRN_SERVICEINSTANCE', 'SZCONTRACTID', null, 'A');
create or replace procedure Mig_Compare_Table_Data(p_szTableKey VARCHAR2)
as
v_qry_source VARCHAR2(10000);
v_qry VARCHAR2(10000);
TYPE T_MY_LIST IS TABLE OF VARCHAR2(32000);
v_cols_source T_MY_LIST; -- list of columns
v_qry_destination VARCHAR2(1000);
v_cols_destination T_MY_LIST;
v_cols_flag T_MY_LIST;
v_tab_source VARCHAR2(200);
v_tab_destination VARCHAR2(200);
v_szWhereClause VARCHAR2(300);
v_final_query VARCHAR2(10000);
begin
v_qry_source :=' ';
select szSourceTableName,m.szdestinationtablename,szWhereClause into v_tab_source,v_tab_destination, v_szWhereClause
from Mig_Tab_Compare M where M.Sztablekey = p_szTableKey and cStatus='A' and M.szWhereClause is not null;
execute immediate ' select m.szsourcecolumnname ,m.szdestinationcolumnname '
||' from Mig_Tab_Compare m where szTableKey=upper('''||p_szTableKey||''')'
bulk collect into v_cols_source,v_cols_destination;--,v_cols_flag ;
/*
execute immediate ' select szDestinationColumnName '
||' from Mig_Tab_Compare where szTableKey=upper('''||p_szTableKey||''')'
bulk collect into v_cols_destination; */
FOR I in 1..v_cols_source.count loop -- dbms_output.put_line(v_cols(i)||'.'||v_types(i));
v_qry_source := v_qry_source||v_tab_source||'.'||v_cols_source(i)||',' ||v_tab_destination||'.'||v_cols_destination(I)||','|| 'decode(nvl('||v_tab_source||'.'||v_cols_source(i)||',0)'||','||'nvl('||v_tab_destination||'.'||v_cols_destination(I)||',0)'||',''Y'',''N'') Flag,' ;
end loop;
v_qry_source := v_qry_source ||'!';
v_qry_source := replace (v_qry_source, ',!');
v_final_query := 'create table '|| p_szTableKey|| '_Compare as Select '|| v_qry_source || ' From ' ||v_tab_source||',' || v_tab_destination ||' Where '|| v_szWhereClause ;
--v_qry:= ' select '||v_qry_source||' from '||v_tab_source||';';
dbms_output.put_line (v_final_query);
end;
I am facing the issue for processing row num 4 from mig_tab_compare , where destination column is containing more tables
April 08, 2017 - 5:15 am UTC
The outer join is for the comparison, so all you need to do is make sure the inputs are what you want to compare, so
select
decode(t1.col1,t2.col1,'Same','Changed') col1_status,
decode(t1.col2,t2.col2,'Same','Changed') col2_status,
decode(t1.col3,t2.col3,'Same','Changed') col3_status,
decode(t1.col4,t2.col4,'Same','Changed') col4_status,
...
from t1
full outer join t2
on ( [join cols ] )
can just as easily be
select
decode(t1.col1,t2.col1,'Same','Changed') col1_status,
decode(t1.col2,t2.col2,'Same','Changed') col2_status,
decode(t1.col3,t2.col3,'Same','Changed') col3_status,
decode(t1.col4,t2.col4,'Same','Changed') col4_status,
...
from
( select * from t1 )
full outer join
( select ... from t2,t3,t4,t5 where ... )
on ( [join cols ] )
So your two comparisons do not have to be single tables.
If each *column* needs to be compared with *different* sources, then you'd need to do them in separate queries.