Hello, Ask TOM Team.
I have two tables:
CREATE TABLE USER.DOC_DETAILS
(CLIENT_ID NUMBER NOT NULL,
DOC VARCHAR2(13 BYTE) NOT NULL,
REGISTER_DATE DATE NOT NULL,
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2),
TAX3_AMMOUNT NUMBER(18,2)
);
CREATE TABLE USER.DOC_SUMMARY
(CLIENT_ID NUMBER NOT NULL,
PERIOD VARCHAR2(6 BYTE) NOT NULL,
DOC_QUANTITY NUMBER, --number of docs
DOC_AMMOUNT NUMBER(18,2),
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2)
TAX3_AMMOUNT NUMBER(18,2)
);
Requirement: I need to read USER.DOC_DETAILS table and load/update data every day in USER.DOC_SUMMARY table grouping by CLIENT_ID and PERIOD (YYYYMM from REGISTER_DATE) summarizing the amount columns.
A row per CLIENT_ID and PERIOD must exists in USER.DOC_SUMMARY table. Every day data is inserted in USER.DOC_DETAILS so the procedure must update the DOC_QUANTITY column and amount columns.
Note: Docs from January can arrive to USER.DOC_DETAILS today (April) so, row with CLIENT_ID x and PERIOD 202001 must be updated in USER.DOC_SUMMARY table.
Thanks in advanced.
Regards,
Instead of a summary table, I'd create a materialized view. Refreshing this populates the MV with the results of the query.
Here's an example:
create table periods (
period date not null
primary key
check ( trunc ( period, 'mm' ) = period )
);
create table doc_details (
client_id number not null,
doc varchar2(13 byte) not null,
register_date date not null,
tax1_ammount number(18,2),
tax2_ammount number(18,2),
tax3_ammount number(18,2),
primary key ( client_id, doc )
);
create materialized view log on doc_details
with rowid, primary key (
register_date, tax1_ammount, tax2_ammount, tax3_ammount
)
including new values;
create materialized view log on periods
with rowid, primary key
including new values;
insert into periods
select add_months ( date'2020-01-01', level - 1 )
from dual
connect by level <= 12;
create materialized view doc_summary
enable query rewrite
as
select p.period, client_id, count(*),
sum ( tax1_ammount ) tax1_ammount,
sum ( tax2_ammount ) tax2_ammount,
sum ( tax3_ammount ) tax3_ammount
from periods p
left join doc_details dd
on p.period = trunc ( register_date, 'mm' )
group by p.period, client_id;
exec dbms_mview.refresh ( 'doc_summary', 'C' );
select * from doc_summary
where period between trunc ( sysdate, 'mm')
and add_months ( trunc ( sysdate, 'mm'), 1 );
PERIOD CLIENT_ID COUNT(*) TAX1_AMMOUNT TAX2_AMMOUNT TAX3_AMMOUNT
01-APR-2020 00:00:00 <null> 1 <null> <null> <null>
01-MAY-2020 00:00:00 <null> 1 <null> <null> <null>
insert into doc_details
values ( 1, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details
values ( 1, 'doc2', sysdate + 30, 1, 2, 3 );
insert into doc_details
values ( 2, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details
values ( 2, 'doc2', sysdate + 30, 1, 2, 3 );
commit;
exec dbms_mview.refresh ( 'doc_summary', 'C' );
select * from doc_summary
where period between trunc ( sysdate, 'mm')
and add_months ( trunc ( sysdate, 'mm'), 1 );
PERIOD CLIENT_ID COUNT(*) TAX1_AMMOUNT TAX2_AMMOUNT TAX3_AMMOUNT
01-APR-2020 00:00:00 2 1 1 2 3
01-MAY-2020 00:00:00 2 1 1 2 3
01-APR-2020 00:00:00 1 1 1 2 3
01-MAY-2020 00:00:00 1 1 1 2 3If you can change the query so it's an inner join, then you could make the MV fast refresh on commit. And the database will keep this in sync for you automatically.
If you can't, run the (complete) refresh at the end of each day.