Update statement errors, parenthesis, failed validation ORACLE

Question

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

These are DML statements, not triggers.

Jeffrey Kemp
– Jeffrey Kemp

2012-03-25 13:19:56 +00:00
Commented Mar 25, 2012 at 13:19 — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 25, 2012 at 13:19
yes indeed, i forgot to update the title

David Garcia
– David Garcia

2012-03-25 16:34:50 +00:00
Commented Mar 25, 2012 at 16:34 — David Garcia
– David Garcia, Commented Mar 25, 2012 at 16:34

Codo · Accepted Answer · 2012-03-24 18:45:51Z

1

NVL takes at least two parameters. But the NVLs at the beginning on the line have only one.

You probably want:

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

Next time, please add the error message to the question.

answered Mar 24, 2012 at 18:45

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update statement errors, parenthesis, failed validation ORACLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related