ORA-00904 error while querying the Oracle database table

Question

I am newbie with oracle DB and I need to List the Number of departments per region in the region table.

This is my SQL statement:

 select r.region_id, count(d.department_id) "Number of Department"
 from region r, department d
 where r.region_id = d.region_id
 group by r.region_id

I am getting this error:

ORA-00904: "D"."region_id": invalid identifier

How can I correct this error?

• Cristian Florescu: Use quotes on columns names in all your select Ex. r."region_id"=d."region_id"

Answer 1

When ORA-00904: invalid identifier occurs, we must enter a valid column name as it is either missing or the one entered is invalid. This error most commonly happens when we are referencing an invalid alias in a select statement. Oracle's voice on ORA-00904 error:

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters ,ドル _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names must

begin with a letter.

consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them.

be less than or equal to thirty characters.