java.sql.SQLException: ORA-00904: "column": invalid identifier

This exception occurs in several situations.

1. When GROUP BY clause is used in a SQL, the column names in the GROUP BY clause have to be real column names in the selected table even though alias are used.

For example, the following SQL gives the "invalid identifier" exception.

SELECT nvl(p1.NAMELAST, ' ') "NAMELAST1", nvl(p1.NAMEFIRST, ' ') "NAMEFIRST1", nvl(p1.NAMEMIDDLE, ' ') "NAMEMIDDLE1" FROM PERSON p1 GROUP BY NAMELAST1, NAMEFIRST1, NAMEMIDDLE1;

ERROR at line 1:
ORA-00904: "NAMEMIDDLE1": invalid identifier

The following 2 SQLs both work fine.

SELECT nvl(p1.NAMELAST, ' ') "p1.NAMELAST", nvl(p1.NAMEFIRST, ' ') "p1.NAMEFIRST", nvl(p1.NAMEMIDDLE, ' ') "p1.NAMEMIDDLE" FROM PERSON p1 GROUP BY p1.NAMELAST, p1.NAMEFIRST, p1.NAMEMIDDLE;

SELECT nvl(p1.NAMELAST, ' ') NAMELAST, nvl(p1.NAMEFIRST, ' ') NAMEFIRST, nvl(p1.NAMEMIDDLE, ' ') NAMEMIDDLE FROM PERSON p1 GROUP BY NAMELAST, NAMEFIRST, NAMEMIDDLE;

2. The column name used is invalid.

A valid column name must meet the following criteria

• Cannot be a reserved word such as ALL, DATE, and COMMENT.
• Must start with a letter
• Consists only alphanumeric characters and these special characters: $, _, #
• The column name must be enclosed in double quotation marks if other characters are used.

For example the following SQL may generate the "invalid identifier" exception.

CREATE table person (
          personID          NUMBER,
          NAME             VARCHAR(30),
          BORN@          DATE
);

3. Mix INNER JOIN and WHERE clauses.

The following SQL will throw such an exception.

SELECT count(*)
FROM PERSON p, SINGER a
INNER JOIN DANCER b on b.ID = p.PERSONID
WHERE a.ID = p.PERSONID;

The following is odd but works.

SELECT count(*)
FROM PERSON p
INNER JOIN DANCER b on b.ID = p.PERSONID,
SINGER a
WHERE a.ID = p.PERSONID;

4. When double quotation marks are used for column names.

SQL by default is case insensitive for column names. However, if the column names are enclosed in double quotation marks and contains lower case characters, you have to use the double quotation marks and the lower case characters whenever the column name is referenced.

For example, the following throws such an "invalid identifier" exception.

CREATE table person (
          "person_ID"          NUMBER,
          NAME             VARCHAR(30),
          "BORN@"          DATE
);

SELECT NAME from PERSON where person_ID = 5;

ERROR at line 1:
ORA-00904: "PERSON_ID": invalid identifier

The following works fine.

          SELECT NAME from PERSON where "person_ID" = 5;

However, it is better not to use double quotation marks for column names.

          Previous <

-----------------------------------------------------------------------------------------------------
          

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.