| filling summary table from detail and updating the detail with summary reference [message #630479] |
Mon, 29 December 2014 02:51  |
 |
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have a summary table and a detail table, I need to fill the summary table with the sum of the amounts received in the same day from the detail table, and also need to fill the detail table back with a reference to the summary record inserted in the summary table. I wonder if this can be done without using PL/SQL or not.
My example is:
CREATE TABLE TST_SUMMARY
(
SUMMARY_ID NUMBER PRIMARY KEY,
TYPE NUMBER,
TRANS_DATE DATE,
TOTAL_AMOUNT NUMBER
);
CREATE SEQUENCE SEQ_TEST START WITH 1
NOCACHE
NOCYCLE;
CREATE OR REPLACE TRIGGER TRG_TEST
BEFORE INSERT ON TST_SUMMARY FOR EACH ROW
BEGIN
SELECT SEQ_TEST.NEXTVAL INTO :NEW.SUMMARY_ID FROM DUAL;
END;
CREATE TABLE TST_DETAIL
(
DETAIL_ID NUMBER,
AMOUNT NUMBER,
TRANS_DATE DATE,
FK_SUMMARY_ID NUMBER REFERENCES TST_SUMMARY,
PRIMARY KEY (DETAIL_ID, TRANS_DATE)
);
INSERT ALL
INTO TST_DETAIL VALUES (1,1001.5,TO_DATE('01-12-2014','DD-MM-YYYY'),NULL)
INTO TST_DETAIL VALUES (2,2000.5,TO_DATE('01-12-2014','DD-MM-YYYY'),NULL)
INTO TST_DETAIL VALUES (3,3009.9,TO_DATE('01-12-2014','DD-MM-YYYY'),NULL)
INTO TST_DETAIL VALUES (4,4000.1,TO_DATE('01-12-2014','DD-MM-YYYY'),NULL)
INTO TST_DETAIL VALUES (5,9001.9,TO_DATE('05-12-2014','DD-MM-YYYY'),NULL)
INTO TST_DETAIL VALUES (6,1009.1,TO_DATE('05-12-2014','DD-MM-YYYY'),NULL)
SELECT * FROM DUAL;
and then I need to:
INSERT INTO TST_SUMMARY(TYPE, TRANS_DATE, TOTAL_AMOUNT )
(SELECT 4, TRANS_DATE, SUM(AMOUNT) FROM TST_DETAIL GROUP BY TRANS_DATE);
--BUT I DONT KNOW HOW TO UPDATE THE DETAIL TABLE (DETAIL.FK_SUMMARY_ID)
Appreciate your help.
Thanks,
Ferro
|
|
|
|
|
|
|
|
| Re: filling summary table from detail and updating the detail with summary reference [message #630649 is a reply to message #630481] |
Thu, 01 January 2015 13:18  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Normally, summary rows are derived from detail rows. That means they share an already existing key. You have indicated that you are not willing to do that and as a result you have decided to use a "non-standard" way of doing summaries which requires updates to the detail data in order to retain some kind of auditable linkage. There are two obvious problems with this however.
1. This will put a heavy strain on your system. If you ever try this with large detail tables you will end up with lots of updating which will burden your logging system and auditing system and replication system and pretty much anything else that needs to monitor in some way what happens to your data.
2. It may be an untenable design anyway. As soon as you develop a second summary that requires use of detail rows that were summed up into the first summary you will have detail rows that participate in multiple summaries. What do you update your detail row with then? Now you are faced with needed to add new "association" tables to fix it.
If you stick to the traditional design of using the group by key as the key of your summaries then these problems go away. It does not matter now may summaries the detail rows participate in, and there is no need to go back and update the detail rows in the first place.
So I would suggest you revisit the design as it appears very flawed to me. Put the full key into the summary table and let the headaches go away.
Kevin
|
|
|
|