Sum values from two tables ORACLE

aggregateoracleoracle-11g-r2

I want 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'

Best Answer

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;
/
Related Question