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 |
|
OraFerro
Messages: 433 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 |
John Watson
Messages: 8937 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 #677372 is a reply to message #677371] |
Sun, 15 September 2019 02:21 |
|
OraFerro
Messages: 433 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 #677702 is a reply to message #677319] |
Tue, 08 October 2019 03:51 |
|
OraFerro
Messages: 433 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
|
|
|
Goto Forum:
Current Time: Mon Jun 17 00:53:27 CDT 2024
|