2

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?

1
  • Use quotes on columns names in all your select Ex. r."region_id"=d."region_id" Dec 3, 2019 at 19:27

1 Answer 1

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.

  • 0

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

    Not the answer you're looking for? Browse other questions tagged or ask your own question.