Oracle SQL Error : Unable to execute Subquery

Question

I am trying to execute below Oracle SQL query

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

user5683823 · Accepted Answer · 2021-05-14 03:14:40Z

2

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!

answered May 14, 2021 at 3:14

user5683823

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

Collectives™ on Stack Overflow

Oracle SQL Error : Unable to execute Subquery

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