I have two tables.
Customer_Master has 3 Columns c_id, c_addr, c_trans
Customer_Address has 3 Columns c_id, c_addr, pr
I fetch Customer_Master into Cursor C1 and Customer_address into Cursor C2. The Select Statement in Cursor C2 has a Where conditions which is fed from Columns c_id and c_addr fetched from Cursor C1.
Cursor C1 Select Statement:
Select c_trans, c_id, c_addr, from customer_master
c_trans is Primary Key. Not Null and Unique
Cursor C2 Select Statment:
Select pr from customer_address where c_id = cid and c_addr = cad
pr Contains only the Values True or False.
Now, I have to check to check weather pr contains the value 'True'. If true, nothing has to be done.
If false, It has to update c_addr of customer_master with the value of customer_address . c_addr which has pr as True)
`Update customer_master
Set c_addr = (select c_addr from customer_address where pr = 'TRUE' and c_id = cid)
where c_trans = ctrans`
How to implement this.
EDITED:
My Code;
`declare
cid number;
cadd number;
ctras number;
cr varchar(2);
cad number;
cursor c1 IS
select c_tras, c_id, c_add from customer_master;
cursor c2 IS
select c_address, cr from customer_address where c_id = cid;
begin
open c1;
open c2;
LOOP
fetch c1 into ctras, cid, cadd;
fetch c2 into cad, cr;
if cr='N'
THEN
update customer_master set c_address = (select c_address from customer_address where cr = 'Y' and c_id = cid) where c_tras = ctras;
END IF;
END LOOP;
END;`
This works perfect.
declare
cid number;
cadd number;
ctras number;
pr varchar(2);
vad number;
cursor c1 IS
select ac_tras, cust_id, cust_addr from customer_master;
cursor c2 IS
select pr_adr from customer_address where cust_id = cid and cust_addr = cadd;
BEGIN
open c1;
LOOP
fetch c1 into ctras, cid, cadd;
EXIT WHEN C1%NOTFOUND;
OPEN c2;
LOOP
fetch c2 into pr;
if pr='Y'
THEN EXIT ;
ELSE
UPDATE customer_master
set cust_addr = (select cust_addr from customer_address where pr_adr = 'Y' and cust_id = cid) where ac_tras = ctras;
END IF;
EXIT WHEN C2%NOTFOUND;
END LOOP;
Close C2;
END LOOP;
CLOSE C1;
END;