Invalid Identifier SQL ORA-00904

Question

Hello I am trying to do a query that I have created to count a country ID, however I am getting the ORA-00904 error with my OEHR_LOCATIONS.REGION_ID on the Group BY line 4.

SELECT OEHR_COUNTRIES.COUNTRY_ID, REGION_ID, COUNT (OEHR_COUNTRIES.COUNTRY_ID)
FROM OEHR_COUNTRIES, OEHR_LOCATIONS
WHERE OEHR_COUNTRIES.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
GROUP BY OEHR_COUNTRIES.COUNTRY_ID, OEHR_LOCATIONS.REGION_ID

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-12-05T03:27:20

I can't speak for what might be causing your error, but I would encourage you to write the query more like this:

SELECT c.COUNTRY_ID, l.REGION_ID, COUNT(*)
FROM OEHR_COUNTRIES c JOIN
     OEHR_LOCATIONS l
     ON c.COUNTRY_ID = l.COUNTRY_ID
GROUP BY c.COUNTRY_ID, l.REGION_ID;

Notes:

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.
Table aliases make the table easier to write and to read.
There is no need to count a column that is never NULL. That is what COUNT(*) does.

Invalid Identifier SQL ORA-00904

1 Answers