CODEWITHSUNDEEP
sqloracle-database
user1056798
user1056798

Reputation: 235

invalid identifier in WHERE clause

I'm trying to write the SQL to copy a row from one table to another but I keep getting an invalid identifier in the WHERE clause. I'm using oracle apex.

Here is my code:

INSERT INTO CRIMECLOSED (crimeClosedID, crimeName, crimeDate, crimeNotes, 
   outsideSourceDescription, dateClosed, relatedCrimes, 
   staffID, crimeTypeID, locationID)  
SELECT CRIMEOPEN.crimeOpenID, CRIMEOPEN.crimeName, CRIMEOPEN.crimeDate, 
       CRIMEOPEN.crimeNotes,  CRIMEOPEN.outsideSourceDescription, CURDATE(), 
       CRIMEOPEN.relatedCrimes, CRIMEOPEN.staffID, CRIMEOPEN.crimeTypeID, 
       CRIMEOPEN.locationID
FROM CRIMEOPEN
WHERE CRIMEOPEN.crimeOpenID = '1';

CRIMEOPEN table

CREATE TABLE  "CRIMEOPEN" 
(   "crimeOpenID" VARCHAR2(5), 
"crimeName" VARCHAR2(20), 
"crimeDate" DATE, 
"crimeNotes" VARCHAR2(200), 
"outsideSourceDescription" VARCHAR2(200), 
"relatedCrimes" VARCHAR2(5), 
"staffID" VARCHAR2(5), 
"crimeTypeID" VARCHAR2(5), 
"locationID" VARCHAR2(5), 
 CONSTRAINT "CRIMEOPEN_PK" PRIMARY KEY ("crimeOpenID") ENABLE
 )

The error I get is:

ORA-00904: "CRIMEOPEN"."CRIMEOPENID": invalid identifier

I think the error is trying to say that 'crimeOpenID' is not a column in 'CRIMEOPEN' but it is. Any help please?

Upvotes: 1

Views: 3559

Answers (1)

Eduard Uta
Eduard Uta

Reputation: 2607

If you use double quotes when declaring the columns of the table then the same format needs to be used also when "using" the columns in various statements.

So in order to fix the issue you should wrap all columns in double quotes in the select statement (something like below - applied only to the select statement).

INSERT INTO CRIMECLOSED (crimeClosedID, crimeName, crimeDate, crimeNotes, 
   outsideSourceDescription, dateClosed, relatedCrimes, 
   staffID, crimeTypeID, locationID)  
SELECT "CRIMEOPEN"."crimeOpenID", "CRIMEOPEN"."crimeName", "CRIMEOPEN"."crimeDate", 
       "CRIMEOPEN"."crimeNotes",  "CRIMEOPEN"."outsideSourceDescription", CURDATE(), 
       "CRIMEOPEN"."relatedCrimes", "CRIMEOPEN"."staffID", "CRIMEOPEN"."crimeTypeID", 
       "CRIMEOPEN"."locationID"
FROM "CRIMEOPEN"
WHERE "CRIMEOPEN"."crimeOpenID" = '1';

Upvotes: 2

Related Questions