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 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); 
share|improve this question
1  
I don't see a trigger, only an UPDATE statement. –  a_horse_with_no_name Mar 24 '12 at 23:13
    
yes indeed, got confused very tired :( –  Jose David Garcia Llanos Mar 25 '12 at 6:39
1  
So, your question is only about the UDATE statement? –  a_horse_with_no_name Mar 25 '12 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 –  Jose David Garcia Llanos Mar 25 '12 at 9:09
    
Update, sorry i forgot to edit the title and content –  Jose David Garcia Llanos Mar 25 '12 at 16:38
add comment

1 Answer

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

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.