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;
/
The query is syntactically correct SQL even if table_b
does not have a name
column. The reason is scope resolution.
When the query is parsed, it is first checked whether table_b
has a name
column. Since it doesn't, then table_a
is checked. It would throw an error only if neither of the tables had a name
column.
Finally the query is executed as:
select a.*
from table_a a
where a.name in (select a.name
from table_b b
);
As for the results the query would give, for every row of table_a
, the subquery (select name from table_b)
- or (select a.name from table_b b)
- is a table with a single column with the same a.name
value and as many rows as table_b
. So, if table_b
has 1 or more rows, the query runs as:
select a.*
from table_a a
where a.name in (a.name, a.name, ..., a.name) ;
or:
select a.*
from table_a a
where a.name = a.name ;
or:
select a.*
from table_a a
where a.name is not null ;
If table_b
is empty, the query will return no rows (thnx to @ughai for pointing that possibility).
That (the fact that you don't get an error) is probably the best reason that all column references should be prefixed with the table name/alias. If the query was:
select a.* from table_a where a.name in (select b.name from table_b);
you would have got the error straight away. When table prefixes are omitted, it is not difficult for such mistakes to happen, especially in more complex queries, and even more important, go unnoticed.
Read also in Oracle docs: Resolution of Names in Static SQL Statements the similar example B-6 in Inner capture and the recommendations in the Avoiding Inner Capture in SELECT and DML Statements paragraphs:
Qualify each column reference in the statement with the appropriate table alias.
Best Answer
Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES
The bug is "still being worked on".
The workaround is obvious: use different aliases.
Non-ANSI join throws an error as expected.
Seems to be fixed in 18.1 and 18.2 as well.