Purchase_Mas contains data of purchase master and payment_amt contains data of payment received from party.
Cursor c2 does not return any value when not found in payment but still i want to some calculation happen even data not found in payment table.
run following script
and when you fire a query " select * from purchase_mas_tmp ; "
Look at ( ID =5 and party code=12 ) and ( ID = 6 and party code= 14 ) when payment amount not found in cursor c2 but i want to os_amt display as 10000 for 5 and 20000 for 6 id of payment.
so how its possible
create table PURCHASE_MAS
(
id NUMBER,
party_code NUMBER,
total_pcs NUMBER,
total_amt NUMBER,
purchase_date DATE,
reg_flg CHAR(1),
discount_amt NUMBER
);
create table PAYMENT
(
id NUMBER,
party_code NUMBER,
payment_date DATE,
payment_amt NUMBER
);
create global temporary table PURCHASE_MAS_TMP
(
id NUMBER,
party_code NUMBER,
total_pcs NUMBER,
total_amt NUMBER,
purchase_date DATE,
reg_flg CHAR(1),
payment_date DATE,
payment_amt NUMBER,
os_amt NUMBER,
discount_amt NUMBER
)
on commit preserve rows;
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (5, 12, 456, 10000, to_date('01-08-2018', 'dd-mm-yyyy'), 'Y', 100);
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (6, 14, 878, 20000, to_date('21-08-2018', 'dd-mm-yyyy'), 'N', 200);
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (1, 11, 457, 30000, to_date('11-08-2018', 'dd-mm-yyyy'), 'Y', 300);
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (2, 12, 658, 40000, to_date('10-08-2018', 'dd-mm-yyyy'), 'Y', 400);
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (3, 11, 1454, 50000, to_date('07-08-2018', 'dd-mm-yyyy'), 'Y', 500);
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (4, 13, 1254, 60000, to_date('18-08-2018', 'dd-mm-yyyy'), 'N', 600);
insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (1, 11, to_date('01-09-2018', 'dd-mm-yyyy'), 2500);
insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (2, 12, to_date('12-09-2018', 'dd-mm-yyyy'), 3000);
insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (3, 11, to_date('11-09-2018', 'dd-mm-yyyy'), 30000);
insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (4, 13, to_date('21-09-2018', 'dd-mm-yyyy'), 400);
declare
cursor c1 is
select id, party_code, total_pcs, total_amt, purchase_date, reg_flg , discount_amt
from purchase_mas;
cursor c2 is
select id, party_code, sum(payment_amt) payment_amt from payment group by id, party_code ;
begin
for i in c1 loop
insert into purchase_mas_tmp
(id, party_code, total_pcs, total_amt, purchase_date, reg_flg,discount_amt)
values
(i.id,
i.party_code,
i.total_pcs,
i.total_amt,
i.purchase_date,
i.reg_flg,
i.discount_amt);
end loop;
for i in c2 loop
update purchase_mas_tmp tbl
set payment_amt = nvl(i.payment_amt,0),
os_amt = tbl.total_amt - nvl(tbl.discount_amt,0) - nvl(i.payment_amt,0)
where id = i.id and
party_code = i.party_code ;
end loop;
end;
-- select * from purchase_mas_tmp ;
/*
drop table PURCHASE_MAS purge ;
drop table PAYMENT purge ;
drop table purchase_mas_tmp purge ;
*/
So you want to set the payment and os amounts when there's no matching payment row?
The problem with using loops to do this is there's no source row. So there's no iteration of the loop for the missing rows
The other problem with using loops is it's slooooooowwwwwwww.
You can fix boths of these issues by:
- Having a single query that outer joins payment to purchase_mas
- Use analytic functions to calculate the payment sum (assuming a 1:1 mapping from purchases to payments; if a purchase can have many payments you'll need to group by instead)
Make these changes and all you need to do is insert the result of this query:
select m.id, m.party_code, m.total_pcs, m.total_amt, m.purchase_date, reg_flg , discount_amt,
nvl ( sum(payment_amt) over ( partition by m.id, m.party_code), 0 ) payment,
total_amt - nvl(discount_amt,0) - nvl ( sum(payment_amt) over ( partition by m.id, m.party_code), 0 ) os_amt
from purchase_mas m
left join payment p
on m.id = p.id
and m.party_code = p.party_code;
ID PARTY_CODE TOTAL_PCS TOTAL_AMT PURCHASE_DATE REG_FLG DISCOUNT_AMT PAYMENT OS_AMT
1 11 457 30000 11-AUG-2018 00:00:00 Y 300 2500 27200
2 12 658 40000 10-AUG-2018 00:00:00 Y 400 3000 36600
3 11 1454 50000 07-AUG-2018 00:00:00 Y 500 30000 19500
4 13 1254 60000 18-AUG-2018 00:00:00 N 600 400 59000
5 12 456 10000 01-AUG-2018 00:00:00 Y 100 0 9900
6 14 878 20000 21-AUG-2018 00:00:00 N 200 0 19800
No need for any explicit cusors!