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