I am trying to execute below Oracle SQL query
x
select m.*, n.region_building_count from
(SELECT
a.BUILDING_ID as "Building Id",
d.Building_name as "Building Name",
d.ADDRESS as "Building Address",
d.REGION as "Region",
d.Country as "Country",
d.State as "State",
d.City as "City"
FROM Company a, LOCATION d
where 1=1
and a.Building_ID like 'BLD-%') m
left JOIN
(select count(building_id) region_building_count, region
from LOCATION l
where l.Building_ID like 'BLD-%' group by region) n
on m.region = n.region
But keep getting error like
Oracle database error 904: ORA-00904: "M"."REGION": invalid identifier
Any idea what I am missing
Advertisement
Answer
If you use double-quotes when you define an alias like "Region"
(which you should never do!), the alias becomes case-sensitive. When you reference m.region
, that is not in double-quotes, the column name is automatically converted to upper-case REGION
, which does not match Region
.
Just don’t use double-quotes around the aliases in the query and this problem will be cured. I didn’t check the rest of the query to see if you will run into additional errors.
Alternatively, you could use m."Region"
everywhere (in double quotes). Bad practice!