Skip to Main Content
  • Questions
  • Update rows when cursor returns no data

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 19, 2020 - 5:19 am UTC

Last updated: October 20, 2020 - 10:05 am UTC

Version: 12C

Viewed 1000+ times

You Asked

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 ;
  
  */

and Chris said...

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!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.