ORA-00904: invalid identifier
Your problem is those pernicious double quotes.
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>
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> 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
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'
Why do I have ORA-00904 even when the column is present?
ORA-00904-invalid identifier
errors are frequently caused by case-sensitivity issues. Normally, Oracle tables and columns are not case sensitive and cannot contain punctuation marks and spaces. But if you use double quotes to create a quoted identifier, that identifier must always be referenced with double quotes and with the correct case. For example:
create table bad_design("goodLuckSelectingThisColumn " number);
ORA-00904 Invalid Identifier: But Column Name is Valid
Strings in Oracle are to be enclosed into single quotes, not double ones.
Therefore, almost the whole WHERE
clause is wrong, should've been
WHERE
(((PRODDTA.F4211.SDLNTY != 'T'))) AND
(((PRODDTA.F4211.SDADDJ IS NOT NULL))) AND
(((PRODDTA.F4211.SDLTTR < '980'))) AND
(((PRODDTA.F4211.SDLITM != 'CFO247'))) AND
(((PRODDTA.F4211.SDSHAN != '70789701'))) AND
(((PRODDTA.F4211.SDSHAN != '70789702'))) AND
(((PRODDTA.F4211.SDSHAN != '70626701')))
Error you got is kind of misleading; as you enclosed 70626701
into double quotes, Oracle presumed it was a column name, while it was not.
How to solve ORA-00904: : invalid identifier while creating table?
Use Oracle's datatypes. Define number columns with 0
precision to make them integers. Use varchar2
rather than varchar
(although Oracle will change it for you).
create table eas_citizen_text_info (
UID number(12,0) not null Primary key,
Gender varchar2(15),
First_name varchar2(30),
Middle_name varchar2(30),
Last_name varchar2(30),
DOB date Not null,
Age number(3,0),
Caste varchar2(30),
Martial_status varchar2(15),
Address varchar2(255),
House_No_Bldg_Apt varchar2(75),
Street_Road_Lane varchar2(75),
Landmark varchar2(75),
Area_locality_sector varchar2(75),
Village_Town_City varchar2(75),
Post_office varchar2(75),
District varchar2(25),
Sub_district varchar2(25),
Pincode number(15,0),
State varchar2(30),
Father_name varchar2(200),
Mother_name varchar2(200),
Guardian_name varchar2(200)
);
However we still get the error:
UID number(12,0) not null Primary key,
*
ERROR at line 2:
ORA-00904: : invalid identifier
SQL>
But Oracle has been generous enough to point out which column is the cause of the error. If we check the documentation we find UID
in the list of SQL reserved keywords. Find that here.
The solution is simple: change UID
to something safe such as CITIZEN_UID
and the table can be created:
SQL> create table eas_citizen_text_info (
2 citizen_UID number(12,0) not null Primary key,
3 Gender varchar2(15),
4 First_name varchar2(30),
5 Middle_name varchar2(30),
6 Last_name varchar2(30),
7 DOB date Not null,
8 Age number(3,0),
9 Caste varchar2(30),
10 Martial_status varchar2(15),
11 Address varchar2(255),
12 House_No_Bldg_Apt varchar2(75),
13 Street_Road_Lane varchar2(75),
14 Landmark varchar2(75),
15 Area_locality_sector varchar2(75),
16 Village_Town_City varchar2(75),
17 Post_office varchar2(75),
18 District varchar2(25),
19 Sub_district varchar2(25),
20 Pincode number(15,0),
21 State varchar2(30),
22 Father_name varchar2(200),
23 Mother_name varchar2(200),
24 Guardian_name varchar2(200)
25 );
Table created.
SQL>
ORA-00904: \d+$: invalid identifier
Single quotes are used for string literals; double quotes are used for identifiers.
Start with a static SQL statement that works:
select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')
Then to make it dynamic you have to make the whole statement a string; you can either escape the existing quotes by doubling them up - note that is two single-quote characters, not one double-quote character:
EXECUTE IMMEDIATE 'select regexp_substr(table_name, ''\d+$'') result
from user_tables
where regexp_like(table_name, ''\d+$'')'
into splitstring;
or use the alternative quoting mechanism:
EXECUTE IMMEDIATE q'^select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')^'
into splitstring;
You don't need dynamic SQL here though, you can just do:
select regexp_substr(table_name, '\d+$') result
into splitstring
from user_tables
where regexp_like(table_name, '\d+$');
db<>fiddle - with a single matching table; if there are none, or more than one, then your query into a scalar variable will fail. Depending on what you're doing with the numeric part of the table name(s), you might want to use a cursor and loop over all the extracted values. That's a separate issue though.
Related Topics
Why Would an in Condition Be Slower Than "=" in Sql
How to Query SQL For a Latest Record Date For Each User
Combining "Like" and "In" For SQL Server
How to Use an Insert Statement'S Output Clause to Get the Identity Value
Error, String or Binary Data Would Be Truncated When Trying to Insert
Remove Identity from a Column in a Table
How to Implement Limit With SQL Server
MySQL Update Column With Value from Another Table
What Is the Benefit of Using "Set Xact_Abort On" in a Stored Procedure
Subquery Using Exists 1 or Exists *
Are "From Table1 Left Join Table2" and "From Table2 Right Join Table1" Interchangeable
Find a String by Searching All Tables in SQL Server
How Important Is the Order of Columns in Indexes
How to Return Result of a Select Inside a Function in Postgresql
SQL Server Recursive Self Join
MySQL Trigger to Prevent Insert Under Certain Conditions
What's the Difference Between Inner Join, Left Join, Right Join and Full Join
How to Load a Large Number of Strings to Match With Oracle Database