Problem
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
One table’s DDL is as follows:
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
Asked by Navaneethan
Solution #1
Those nefarious double quotes are the source of your trouble.
SQL> 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>
We can ignore the case of database object names in Oracle SQL if we construct them with all upper case names or without using double quotes. We are forced to use double quotes and the precise case anytime we refer to the object or its attributes if we use mixed case or lower case in the script and surround the identifiers in double quotes:
SQL> 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> select count(*) from PS_TBL_DEPARTMENT_DETAILS
2 where "Department_Code" = 'BAH'
3 /
COUNT(*)
----------
0
SQL>
tl;dr
In DDL scripts, don’t use double quotes.
(I know that most third-party code generators do, but they are disciplined enough to use UPPER CASE for all of their object names.)
It’s also true in the other direction. If we don’t use double-quotes when creating the table…
create table PS_TBL_DEPARTMENT_DETAILS
( company_code VARCHAR2(255),
company_name VARCHAR2(255),
Cost_Center_Number VARCHAR2(255))
;
…we can use it and its columns in any situation that strikes 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'
Answered by APC
Solution #2
This problem happened in my case owing to the absence of a column name in the table.
I couldn’t find the column mentioned in the mapping hbm file when I ran “explain tablename.”
It worked perfectly when I changed the table.
Answered by Sireesh Yarlagadda
Solution #3
In this situation, the reason was discovered to be a mixed case column name in the DDL used to create the table.
Though you mix “old style” and ANSI joins, you may see the same error notice, even if the DDL was done correctly with uppercase table names. This happened to me, and since I was already on this stackoverflow page, I figured I’d share.
--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) > 5
AND LENGTH(C.LAST_NAME) > 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) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
The two SQL commands above are identical and do not result in an error.
If you try to mix them, you could get lucky, or you might get an 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) > 5
AND LENGTH(C.LAST_NAME) > 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) > 5
AND LENGTH(C.LAST_NAME) > 5
/
And there’s the useless error notice, which doesn’t actually identify the issue:
>[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:
In my case, I was attempting to convert old style joins to ANSI style joins by hand, one table at a time. This appears to be a poor decision. Instead, convert all tables at once, or comment out a table and its where conditions in the old query so you can compare it to the new ANSI query you’re generating.
Answered by qyb2zm302
Solution #4
The column DEPARTMENT CODE does not exist in the table Team. To find the correct column name, look in the table’s DDL.
Answered by Datajam
Solution #5
Are you certain that you have a column? ON YOUR TABLE, DEPARTMENT CODE PS TBL DEPARTMENT DETAILS
Additional details about your ERROR
Answered by mcha
Post is based on https://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier