ORA-00904 invalid identifier

Question

I tried to write the following inner join query using an Oracle database    SELECT Employee EMPLID as EmpID           Employee FIRST NAME AS Name          Team DEPARTMENT CODE AS TeamID           Team Department Name AS teamname  FROM PS TBL EMPLOYEE DETAILS Employee  INNER JOIN PS TBL DEPARTMENT DETAILS Team   ON Team DEPARTMENT CODE   Employee DEPTID   That gives the below error    INNER JOIN PS TBL DEPARTMENT DETAILS Team ON Team DEPARTMENT CODE   Employee DEPTID                                                 ERROR at line 4  ORA-00904   TEAM   DEPARTMENT CODE   invalid identifier   The DDL of one table is   CREATE TABLE  HRMS   PS TBL DEPARTMENT DETAILS       Company Code  VARCHAR2 255      Company Name  VARCHAR2 255      Sector Code  VARCHAR2 255      Sector Name  VARCHAR2 255      Business Unit Code  VARCHAR2 255      Business Unit Name  VARCHAR2 255      Department Code  VARCHAR2 255      Department Name  VARCHAR2 255      HR ORG ID  VARCHAR2 255      HR ORG Name  VARCHAR2 255      Cost Center Number  VARCHAR2 255         VARCHAR2 255    SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS

User · Answer

I had this error when trying to save an entity through JPA   It was because I had a column with  JoinColumn annotation that didn t have  ManyToOne annotation   Adding  ManyToOne fixed the issue

User · Answer

In my case  this error occurred  due to lack of existence of column name in the table   When i executed   describe tablename    i was not able to find the column specified in the mapping hbm file   After altering the table  it worked fine

User · Answer

I was passing the values without the quotes  Once I passed the conditions inside the single quotes worked like a charm    Select   from emp table where emp id 123    instead of the above use this    Select   from emp table where emp id  123

User · Answer

I had the same exception in JPA 2 using eclipse link  I had an  embedded class with one to one relationship with an entity   By mistake  in the embedded class  i had also the annotation  Table  TRADER    When the DB was created by the JPA from the entities it also created a table TRADER  which was a wrong as the Trader entity was embedded to the main entity  and the existence of that table was causing the above exception every time i was trying to persist my entity   After deleting the TRADER table the exception disappered

User · Answer

DEPARTMENT CODE is not a column that exists in the table Team  Check the DDL of the table to find the proper column name

User · Answer

Your problem is those pernicious double quotes     SQL gt  CREATE TABLE  APC   PS TBL DEPARTMENT DETAILS    2      3     Company Code  VARCHAR2 255     4     Company Name  VARCHAR2 255     5     Sector Code  VARCHAR2 255     6     Sector Name  VARCHAR2 255     7     Business Unit Code  VARCHAR2 255     8     Business Unit Name  VARCHAR2 255     9     Department Code  VARCHAR2 255    10     Department Name  VARCHAR2 255    11     HR ORG ID  VARCHAR2 255    12     HR ORG Name  VARCHAR2 255    13     Cost Center Number  VARCHAR2 255    14        VARCHAR2 255   15     16     Table created   SQL gt    Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case  or without using double quotes   If we use mixed case or  lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes   SQL gt  select count    from PS TBL DEPARTMENT DETAILS   2  where Department Code    BAH    3    where Department Code    BAH          ERROR at line 2  ORA-00904   DEPARTMENT CODE   invalid identifier   SQL gt  select count    from PS TBL DEPARTMENT DETAILS   2  where  Department Code     BAH    3       COUNT    ----------          0  SQL gt      tl dr  don t use double quotes in DDL scripts   I know most third party code generators do  but they are disciplined enough to put all their object names in UPPER CASE      The reverse is also true  If we create the table without using double-quotes      create table PS TBL DEPARTMENT DETAILS   company code VARCHAR2 255     company name VARCHAR2 255     Cost Center Number VARCHAR2 255           we can reference it and its columns in whatever case takes our fancy   select   from ps tbl department details       or  select   from PS TBL DEPARTMENT DETAILS        or  select   from PS Tbl Department Details where COMAPNY CODE    ORCL  and cost center number    0980

User · Answer

Also make sure the user issuing the query has been granted the necessary permissions   For queries on tables you need to grant SELECT permission  For queries on other object types  e g  stored procedures  you need to grant EXECUTE permission

User · Answer

Are you sure you have a column DEPARTEMENT CODE on your table PS TBL DEPARTMENT DETAILS     More informations about your 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 d  double   quotation marks        It may not be a reserved word

User · Answer

FYI  in this case the cause was found to be mixed case column name in the DDL for table creation   However  if you are mixing  old style  and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name  This happened to me  and google sent me to this stackoverflow page so I thought I d share since I was here   --NO PROBLEM  ANSI syntax SELECT A EMPLID  B FIRST NAME  C LAST NAME FROM PS PERSON A INNER JOIN PS NAME PWD VW B ON B EMPLID   A EMPLID INNER JOIN PS HCR PERSON NM I C ON C EMPLID   A EMPLID WHERE      LENGTH A EMPLID    9     AND LENGTH B LAST NAME   gt  5     AND LENGTH C LAST NAME   gt  5 ORDER BY 1  2  3    --NO PROBLEM  OLD STYLE deprecated traditional oracle proprietary join syntax SELECT A EMPLID  B FIRST NAME  C LAST NAME FROM PS PERSON A   PS NAME PWD VW B    PS HCR PERSON NM I C  WHERE      B EMPLID   A EMPLID     and C EMPLID   A EMPLID     and LENGTH A EMPLID    9     AND LENGTH B LAST NAME   gt  5     AND LENGTH C LAST NAME   gt  5 ORDER BY 1  2  3     The two SQL statements above are equivalent and produce no error   When you try to mix them you can get lucky  or you can get an Oracle has a ORA-00904 error    --LUCKY  mixed syntax  ANSI joins appear before OLD STYLE  SELECT A EMPLID  B FIRST NAME  C LAST NAME FROM      PS PERSON A     inner join PS HCR PERSON NM I C on C EMPLID   A EMPLID       PS NAME PWD VW B WHERE      B EMPLID   A EMPLID     and LENGTH A EMPLID    9     AND LENGTH B FIRST NAME   gt  5     AND LENGTH C LAST NAME   gt  5    --PROBLEM  mixed syntax  OLD STYLE joins appear before ANSI  --http   sqlfascination com 2013 08 17 oracle-ansi-vs-old-style-joins  SELECT A EMPLID  B FIRST NAME  C LAST NAME FROM      PS PERSON A       PS NAME PWD VW B     inner join PS HCR PERSON NM I C on C EMPLID   A EMPLID WHERE      B EMPLID   A EMPLID     and LENGTH A EMPLID    9     AND LENGTH B FIRST NAME   gt  5     AND LENGTH C LAST NAME   gt  5     And the unhelpful error message that doesn t really describe the problem at all    gt  Error  Script lines  1-12 ------------------------- ORA-00904   A   EMPLID   invalid identifier  Script line 6  statement line 6  column 51    I was able to find some research on this in the following blog post    http   sqlfascination com 2013 08 17 oracle-ansi-vs-old-style-joins    In my case  I was attempting to manually convert from old style to ANSI style joins  and was doing so incrementally  one table at a time  This appears to have been a bad idea  Instead  it s probably better to convert all tables at once  or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing

[sql] ORA-00904: invalid identifier

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to ora-00904