Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to use two values, in two different tables, to be used as a total in a third table.

For instance:

  1. table accommodation and flight
  2. Add value from acc_price with value from flight_price
  3. Add those values up to be used in table reservation subtotal

Things I've tried;

SELECT  F.FLI_PRICE + AC.ACC_PRICEPN
INTO    R.SUBTOTAL
FROM    HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;

UPDATE

I wish to add the code from Leigh into a trigger, i tried the following but got a mutation error

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = 
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);
END;
/

ERROR at line 1:
ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f
ORA-06512: at "LATIN.HR_SUBTOTAL", line 2
ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'
share|improve this question
    
Also asked on SO? stackoverflow.com/questions/9816183/… –  Phil Mar 22 '12 at 11:00
    
RE mutating tables. You'll want to read this: asktom.oracle.com/pls/asktom/… –  Phil Mar 27 '12 at 15:08
add comment

2 Answers 2

up vote 4 down vote accepted

To update a column using a sum of values from other tables that may have nulls, do something like this:

UPDATE Holiday_Reservation R SET Subtotal = 
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
   NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

In a trigger this would look something like this:

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT 
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
      NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
      INTO :new.Subtotal
   FROM dual;
END;
/
share|improve this answer
    
How can I update another column with this new data.. and also how do i overcome the problem where one or the other has nulls –  Jose David Garcia Llanos Mar 22 '12 at 3:15
    
Here is an SQLFiddle demonstration: sqlfiddle.com/#!4/1a6e9/4 –  Leigh Riffel Mar 22 '12 at 18:03
    
Thanks Leigh, do you know if there is anyway to add that code you posted into a trigger so that it does this automatically when a new reservation record is inserted or updated etc, i tried but i got some error, find it above –  Jose David Garcia Llanos Mar 22 '12 at 20:59
    
To modify the values in the table that the trigger is on you need to just assign them. A select statement is one way to assign values, so just change the update into a select statement in the trigger. See my updated answer. –  Leigh Riffel Mar 22 '12 at 21:47
    
I tried is giving me Warning: Trigger created with compilation errors. –  Jose David Garcia Llanos Mar 22 '12 at 21:58
show 3 more comments

You didn't explain what is the join relationship between the tables...

I propose the following:

select F.key_col, F_price + AC_price as total_price
FROM
    (SELECT key_col, sum(F.FLI_PRICE) as F_price
    from FLIGHT F
    group by key_col) as F
  inner join 
    (SELECT key_col, sum(AC.ACC_PRICEPN) as AC_price
    from ACCOMMODATION AC
    group by key_col) as AC 
  on F.key_col = AC.key_col

Add any other tables as you wish. If you want to filter and have better performance, please provide more information.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.