I am trying to execute this query but am getting ORA-00904:"QM"."MDL_MDL_ID":invalid identifier. What is more confusing to me is the main query has two sub queries which only differ in the where clause. However, the first query is running fine but getting error for the second one. Below is the query.
select (
select make_description
from make_colours@dblink1
where makc_id = (
select makc_makc_id
from model_colours@dblink1
where to_char(mdc_id) = md.allocate_vehicle_colour_id
)
) as colour,
(
select make_description
from make_colours@dblink1
where makc_id = (
select makc_makc_id
from model_colours@dblink1
where mdl_mdl_id = qm.mdl_mdl_id
)
) as vehicle_colour
from schema1.web_order wo,
schema1.tot_order tot,
suppliers@dblink1 sp,
external_accounts@dblink1 ea,
schema1.location_contact_detail lcd,
quotation_models@dblink1 qm,
schema1.manage_delivery md
where wo.reference_id = tot.reference_id
and sp.ea_c_id = ea.c_id
and sp.ea_account_type = ea.account_type
and sp.ea_account_code = ea.account_code
and lcd.delivery_det_id = tot.delivery_detail_id
and sp.sup_id = tot.dealer_id
and wo.qmd_id = qm.qmd_id
and wo.reference_id = md.web_reference_id(+)
and supplier_category = 'dealer'
and wo.order_type = 'tot'
and trunc(wo.confirmdeliverydate - 3) = trunc(sysdate)
Oracle usually doesn't recognise table aliases (or anything else) more than one level down in a nested subquery; from the documentation:
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. [...] A correlated subquery conceptually is evaluated once for each row processed by the parent statement.
Note the 'one level' part. So your qm alias isn't being recognised where it is, in the nested subquery, as it is two levels away from the definition of the qm alias. (The same thing would happen with the original table name if you hadn't aliased it - it isn't specifically to do with aliases).
When you modified your query to just have select qm.mdl_mdl_id as Vehicle_colour - or a valid version of that, maybe (select qm.mdl_mdl_id from dual) as Vehicle_colour - you removed the nesting, and the qm was now only one level down from it's definition in the main body of the query, so it was recognised.
Your reference to md in the first nested subquery probably won't be recognised either, but the parser tends to sort of work backwards, so it's seeing the qm problem first; although it's possible a query rewrite would make it valid:
However, the optimizer may choose to rewrite the query as a join or use some other technique to formulate a query that is semantically equivalent.
You could also add hints to encourage that but it's better not to rely on that.
But you don't need nested subqueries, you can join inside each top level subquery:
select (
select mc2.make_description
from model_colours@dblink1 mc1,
make_colours@dblink1 mc2
where mc2.makc_id = mc1.makc_makc_id
and to_char(mc1.mdc_id) = md.allocate_vehicle_colour_id
) as colour,
(
select mc2.make_description
from model_colours@dblink1 mc1,
make_colours@dblink1 mc2
where mc2.makc_id = mc1.makc_makc_id
and mc1.mdl_mdl_id = qm.mdl_mdl_id
) as vehicle_colour
from schema1.web_order wo,
...
I've stuck with old-style join syntax to match the main query, but you should really consider rewriting the whole thing with modern ANSI join syntax. (I've also removed the rogue comma @Serg mentioned, but you may just have left out other columns in your real select list when posting the question.)
You could probably avoid subqueries altogether by joining to the make and model colour tables in the main query, either twice to handle the separate filter conditions, or once with a bit of logic in the column expressions. Once step at a time though...