Procedure Guidance Required
Posted: Tue Oct 30, 2007 11:18 am
Hi all, following is my procedure, As u can see values are going from "pos_test_ext" to "pos_test" table. But one field in "pos_test" table is null named "cond". I want to populated this "cond" field in such a way that if a customer insert asset_type in pos_test_ext table with values(20,30) then cond column is populated with 1 and if customer insert asset_type in pos_test_ext with values(40,50) then "cond" field populated with value 2 and else it will populated with value 3. How can i amend this procedure pls help me in this regard I am very gratefult to you.
Code: Select all
create or replace procedure myprocpos as
begin
execute immediate 'truncate table pos_ins_upd';
execute immediate 'truncate table pos_del_upd';
execute immediate 'truncate table pos_upd';
execute immediate 'truncate table pos_ins';
execute immediate 'truncate table pos_del';
insert into pos_ins_upd
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test_ext
minus
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test;
insert into pos_del_upd
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test
minus
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_test_ext;
insert into pos_upd
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_ins_upd
intersect
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_del_upd;
insert into pos_ins
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_ins_upd
minus
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_upd;
insert into pos_del
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_del_upd
minus
select pos_date,pos_asset_type,pos_ref_no,pos_application from pos_upd;
insert into pos_test
select pos_date,pos_asset_type,pos_ref_no,pos_application,null from pos_test_ext
where pos_asset_type in (select pos_asset_type from pos_ins);
delete from pos_test
where pos_asset_type in (select pos_asset_type from pos_del);
update pos_test
set cond = (select cond
from pos_test_ext
where pos_asset_type = pos_test.pos_asset_type)
where pos_asset_type in (select pos_asset_type from pos_upd);
Commit;
end;