Home » SQL & PL/SQL » SQL & PL/SQL » filling summary table from detail and updating the detail with summary reference (11.2.0.1.0)
filling summary table from detail and updating the detail with summary reference [message #630479] Mon, 29 December 2014 02:51 Go to next message
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 #630480 is a reply to message #630479] Mon, 29 December 2014 03:13 Go to previous messageGo to next message
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
Senior Member
If your dates are always truncated to dd-mm-yyyy, then you can use TYPE and TRANSDATE as your PK and FK columns.
Re: filling summary table from detail and updating the detail with summary reference [message #630481 is a reply to message #630480] Mon, 29 December 2014 03:30 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks John for the reply.

In the real example the detail table has more than field as PK and not all are transferred to the summary table.
Currently I am trying to do the following:
1- remove the trigger on summary table
2- use the sequence to fill all records of the same date in detail table with one value in fk_summary_id
3- insert into the summary table all values including max(fk_summary_id)

I am still searching how to do step 2 in the above. Appreciate if you can 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 Go to previous message
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
Previous Topic: SQL*Net message from db link Doubt
Next Topic: Dynamic monthly sales report
Goto Forum:
  


Current Time: Mon Aug 24 10:01:59 CDT 2026