alines
asked on
Using results of a cursor in another cursor
Hi
I'm looking at creating a procedure that will allow me to check to see if data has changed in a column from staging table compared with the reporting table. Yep simple
select * from stage_tbl s
where exists (select 1 from report_tbl r
where s.id = r.id
and s.compare_col_a <> r.compare_col_a
)
but .... I have a number of columns to compare between the two table and I can't rely on the columns having the same name. So I have created a mapping table that consists of the column names between the two tables. Though how can I make 'and s.compare_col_a <> r.compare_col_a' dynamic so it accepts the other compare columns such as 'and s.compare_col_b <> r.compare_col_b'
Your help would certainly be appreciated
Thanks
I'm looking at creating a procedure that will allow me to check to see if data has changed in a column from staging table compared with the reporting table. Yep simple
select * from stage_tbl s
where exists (select 1 from report_tbl r
where s.id = r.id
and s.compare_col_a <> r.compare_col_a
)
but .... I have a number of columns to compare between the two table and I can't rely on the columns having the same name. So I have created a mapping table that consists of the column names between the two tables. Though how can I make 'and s.compare_col_a <> r.compare_col_a' dynamic so it accepts the other compare columns such as 'and s.compare_col_b <> r.compare_col_b'
Your help would certainly be appreciated
Thanks
alines,
If you just want to know which records contain a change and you don't need to know WHICH column, then you could use the minus (assuming the columns are in the same order or can be arranged to be in the same order). Of course, you need to make sure it's only when the id exists, but that can be done with a simple IN. Set operations are pretty fast:
create table updates_to_process as
select * from staging_table
where id in
(select id from reporting_table)
minus
select * from reporting_table;
ANY change in the data will result in the MINUS not trimming that record from the set going to the "updates_to_process".
Good luck!
If you just want to know which records contain a change and you don't need to know WHICH column, then you could use the minus (assuming the columns are in the same order or can be arranged to be in the same order). Of course, you need to make sure it's only when the id exists, but that can be done with a simple IN. Set operations are pretty fast:
create table updates_to_process as
select * from staging_table
where id in
(select id from reporting_table)
minus
select * from reporting_table;
ANY change in the data will result in the MINUS not trimming that record from the set going to the "updates_to_process".
Good luck!
Are you looking for something like this...
create or replace procedure StgRptCompare(inStgTable in varchar2, inRptTable in varchar2)
is
type cur_ref is ref cursor;
cursor cColList is select colA, colB from table_map where <<some condition>>;
cr cur_ref;
sql_qry varchar2(4000);
begin
sql_qry := 'select * from ' || inStgTable || ' s ' ||
'where exists (select 1 from ' || inRptTable || ' r ' ||
' where s.id = r.id ';
for c1 in cColList loop
sql_qry := sql_qry || 'and s.' || c1.colA || ' <> r.' || c1.colB || ' ';
end loop;
sql_qry := sql_qry || ')';
open cr for sql_qry;
...
ASKER
Hi Ishando
I'm pretty sure this is what I'm looking for. Will it allow me to create a cursor that will loop each of the compare columns 'c1.colA || ' <> r.' || c1.colB ' so that any data that is returned can then be incorporated into an insert statement.
Thanks
Andrew
I'm pretty sure this is what I'm looking for. Will it allow me to create a cursor that will loop each of the compare columns 'c1.colA || ' <> r.' || c1.colB ' so that any data that is returned can then be incorporated into an insert statement.
Thanks
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DrSQL .... Thanks for your help. Why is it the simplest solutions are always the best.
Also thanks to everyone else who replied
Also thanks to everyone else who replied
So I would recommend to select records from you mapping table once to construct this query and then make static SQL statement
select ' and ' || first_table_field || ' <> ' || second_table_field from mapping table.
If you nevertheless need dynamic SQL, you can use the same statement in cursor and construct the query dynamically in your procedure