1

i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.

Original statement

The statement below works flawlessly,

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);

Modified version not working This is the modified version, i want to multiply the first two select with a value in each field for in flight and outfight seats no.

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = 
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0)) +
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0)) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0); 
5
  • 1
    I don't see a trigger, only an UPDATE statement.
    – user1822
    Mar 24, 2012 at 23:13
  • yes indeed, got confused very tired :( Mar 25, 2012 at 6:39
  • 1
    So, your question is only about the UDATE statement?
    – user1822
    Mar 25, 2012 at 7:50
  • indeed just take whats in NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) multiply it by R.IN_FLIGHT_SEATS_NO and do the same for the other and then add the values from the three selects Mar 25, 2012 at 9:09
  • Update, sorry i forgot to edit the title and content Mar 25, 2012 at 16:38

1 Answer 1

1

Editor, that higlights matching braces should do the trick. There are just too many of them. And... You left out the default values in outer nvl() or nvl keyword itself?

...
NVL(
 "nvl here?"(
  (SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0
 )
 * NVL(R.IN_FLIGHT_SEATS_NO,0)
",default value here?") + ...

Go, have a break. This happens to me very often :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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