Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate operation on array - get totals (11.2.0.1.0)
Aggregate operation on array - get totals [message #677319] Thu, 12 September 2019 00:29 Go to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Hi All,

This post is similar to my previous post (http://www.orafaq.com/forum/m/676470/#msg_676470) but this time I am seeking advice regarding a smarter way to accomplish what I want.

I have a query (complex and resource consuming in the real world) that calculates interest rate per withdrawal transaction for each loan. I need to insert the values in Interest (t_Interest) table, meanwhile I need to insert the total interest per loan per date in accounting table (t_ledger_trans).

So I have three options:
1- Create another Stored Procedure (P_GET_DATA_TEST_GRP) to return the same output of P_GET_DATA_TEST but with a group by added in the select:
select loan_id, withd_date, sum(withd_amount * rate/100) tran_total from t_withdrawal tw, t_rate
where tw.withd_date = t_rate.rate_date 
group by loan_id, withd_date;
But this way I will be repeating the expensive query twice which I am trying to avoid.

2- Commit after insertion in T_Interest and query the data I need to fill t_ledger_trans (the accounting table). But this way I will commit data that might not be final which can have serious drawbacks.

3- Find a smarter way to get the totals in P_insert_DATA_TEST while looping on AR_MY_ARRAY.



My example:



create table t_rate 
  (
    Rate_date date,
    rate number(15,3)
  );


create table t_withdrawal
  (
    Loan_id number,
    withd_date date,
    Withd_amount number
  );


create table t_interest
  (
    Loan_id number,
    Interest_date date,
    Interest_amount number(15,3)
  );

create table t_ledger_trans
  (
    Loan_id number,
    Trans_date date,
    tran_total number(15,3)
  );

insert all 
  INTO t_rate values (to_date('01-01-2000','dd-mm-yyyy'), 1.5)
  INTO t_rate values (to_date('02-01-2000','dd-mm-yyyy'), 2.5)
  INTO t_rate values (to_date('03-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_rate values (to_date('04-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_withdrawal values (1, to_date('01-01-2000','dd-mm-yyyy'), 120)
  INTO t_withdrawal values (2, to_date('02-01-2000','dd-mm-yyyy'), 170)
  INTO t_withdrawal values (3, to_date('03-01-2000','dd-mm-yyyy'), 55)
  INTO t_withdrawal values (4, to_date('01-01-2000','dd-mm-yyyy'), 310)
  INTO t_withdrawal values (5, to_date('03-01-2000','dd-mm-yyyy'), 110)
  INTO t_withdrawal values (1, to_date('01-01-2000','dd-mm-yyyy'), 70)
  INTO t_withdrawal values (2, to_date('02-01-2000','dd-mm-yyyy'), 695)
  INTO t_withdrawal values (5, to_date('03-01-2000','dd-mm-yyyy'), 153)
  INTO t_withdrawal values (1, to_date('04-01-2000','dd-mm-yyyy'), 320)
select * from dual;

-- TESTPAC1 specification
CREATE OR REPLACE PACKAGE testPac1 AS

  TYPE REC_COMtest IS RECORD
    (
      Loan_id number,
      T_date date,
      t_amount number(15,3)
    );


  -- Define cursor and table(array) of to hold COM CHARGE data
  TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
  TYPE ARY_COMtest IS TABLE OF REC_COMtest;

  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      );

  PROCEDURE P_insert_DATA_TEST 
  ;
   
end testPac1;

CREATE OR REPLACE PACKAGE BODY testPac1 AS


  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      ) 
      AS 
       MyCur    CUR_COMtest;
       MyCur1   Cur_COMtest;
       I_total_sum number;
     BEGIN
  
      OPEN MyCur FOR
      select loan_id, withd_date, withd_amount * rate/100 Interest_amount from t_withdrawal tw, t_rate
      where tw.withd_date = t_rate.rate_date ;   
      
      FETCH MyCur BULK COLLECT INTO ar_My_Array;
      
      close MyCur;

  END;

  PROCEDURE P_insert_DATA_TEST 
  IS
    AR_MY_ARRAY ARY_COMtest;
 
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount) 
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL; 
   END LOOP; 


  END;

end testPac1;


Thanks,
Ferro
Re: Aggregate operation on array - get totals [message #677330 is a reply to message #677319] Thu, 12 September 2019 06:47 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
I do not understand what you are trying to achieve. However, one thing I can say is that your second optionQuote:
2- Commit after insertion in T_Interest and query the data I need to fill t_ledger_trans (the accounting table). But this way I will commit data that might not be final which can have serious drawbacks.
must be wrong. Why would you commit before running the second query? You should probably be setting your isolation level to SERIALIZABLE before running the first update and have the COMMIT after running the second update. Otherwise you will get inconsistent results.
Re: Aggregate operation on array - get totals [message #677371 is a reply to message #677330] Sun, 15 September 2019 01:03 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Hi John Watson,

Sorry if my post wasnt clear.

1- The procedure (P_GET_DATA_TEST) runs a query to calculate interest (per day per loan) and return results.
2- The procedure (P_insert_DATA_TEST) uses the output of P_GET_DATA_TEST to fill T_INTEREST table.
3- However I also want to fill t_ledger_trans table which is basically the same query run by P_GET_DATA_TEST but grouped by loan and withdrawal date (loan_id and withd_date)
select loan_id, withd_date, sum(withd_amount * rate/100) tran_total from t_withdrawal tw, t_rate
where tw.withd_date = t_rate.rate_date 
group by loan_id, withd_date;
4- In order to get the data that I need in order to fill t_ledger_trans I had the above options:
a- repeat the calculation query (which is an expensive one).
b- use the inserted data in t_interest to re-query and group (which we both agree to be risky)
c- Ask for a smarter way to reuse the fetched array in order to get the needed data.

What do you think?
Thanks,
Ferro
Re: Aggregate operation on array - get totals [message #677372 is a reply to message #677371] Sun, 15 September 2019 02:21 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Dear John Watson and All,

I came up with this idea, it works, but I want your feedback to see if this is the best way or there is another smart way (inside the insert loop).

My change is to use group rollup in order to get the data the suits both tables (t_interest and t_ledger_trans) and use IF statement to control which row is inserted in which table. I order to do so, I had to add another field (SER) which is a row number:

CREATE OR REPLACE PACKAGE testPac1 AS

  TYPE REC_COMtest IS RECORD
    (
      Loan_id number,
      T_date date,
      t_amount number(15,3),
      ser number  --<-- newly added
    );


  -- Define cursor and table(array) of to hold COM CHARGE data
  TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
  TYPE ARY_COMtest IS TABLE OF REC_COMtest;

  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      );

  PROCEDURE P_insert_DATA_TEST 
  ;
   
end testPac1;



CREATE OR REPLACE PACKAGE BODY testPac1 AS


  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      ) 
      AS 
       MyCur    CUR_COMtest;
       MyCur1   Cur_COMtest;
       I_total_sum number;
     BEGIN
   
      OPEN MyCur FOR
--      select loan_id, withd_date, withd_amount * rate/100 Interest_amount from t_withdrawal tw, t_rate
--      where tw.withd_date = t_rate.rate_date ;   
      select loan_id, withd_date, sum(tran_total) tran_total ,ser 
      from 
        (
        select loan_id, withd_date, withd_amount * rate/100 tran_total, row_number() over (partition by loan_id order by withd_date) ser 
          from t_withdrawal tw, t_rate
         where tw.withd_date = t_rate.rate_date
        )
       group by rollup (loan_id, withd_date,ser)
      ; 
      
      FETCH MyCur BULK COLLECT INTO ar_My_Array;
      
      close MyCur;

  END;

  PROCEDURE P_insert_DATA_TEST 
  IS
    AR_MY_ARRAY ARY_COMtest;
 
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);

   FOR I IN 1..ar_My_Array.COUNT LOOP
    if (ar_My_Array(I).Loan_id is not null) and (ar_My_Array(I).t_date is not null) and (ar_My_Array(I).ser is not null) 
    then  
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount) 
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL; 
    elsif (ar_My_Array(I).Loan_id is not null) and (ar_My_Array(I).t_date is not null) and (ar_My_Array(I).ser is null) 
    then 
     INSERT INTO t_ledger_trans (Loan_id, Trans_date, tran_total) 
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL;
    end if;
   END LOOP; 


  END;

end testPac1;

Please tell me your advice
Ferro
Re: Aggregate operation on array - get totals [message #677405 is a reply to message #677372] Tue, 17 September 2019 06:48 Go to previous messageGo to next message
Bill B
Messages: 1917
Registered: December 2004
Senior Member
Have you looked into an on commit Materilized view for your totals. Why run a long intensive query when oracle can just maintain the values on the fly. Make sure it's a fast refresh MVIEW
Re: Aggregate operation on array - get totals [message #677702 is a reply to message #677319] Tue, 08 October 2019 03:51 Go to previous message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Dear John Watson and All,

I am trying to get use of multiset in order to get the total amount per loan as I need it to insert into the Ledger table.

I tried to modify the insert procedure but I get ORA-00902: invalid datatype :
  PROCEDURE P_insert_DATA_TEST 
  IS
    AR_MY_ARRAY ARY_COMtest;
 
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);


   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount) 
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL; 
   END LOOP; 


  select cast ( multiset( select Loan_id, sum(t_amount) amount
              from table( AR_MY_ARRAY )
              group by loan_id
             ) as ARY_COMtest)  --<-- ORA-00902: invalid datatype
  into AR_MY_ARRAY
  from dual;

   FOR I IN 1..ar_My_Array.COUNT LOOP

     INSERT INTO t_ledger_trans (Loan_id, Trans_date, tran_total) 
          SELECT ar_My_Array(I).Loan_id, sysdate, ar_My_Array(I).t_amount
     FROM DUAL;

   END LOOP;


  END;

Please tell me what I am doing wrong.

Thanks,
Ferro
Previous Topic: display Old value and new value as per date in single row
Next Topic: Query Not giving Proper Output and How to write Query In effective way
Goto Forum:
  


Current Time: Tue Dec 10 00:34:10 CST 2019