ORA-00904 when inserting/querying data from tables

问题:

I'm getting the flowing error:

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year, body) VALUES('4585 AW','ALBION','RIEVER',20321,1963, ' ');
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "BODY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

When I do the following

INSERT INTO driver 
(registration, make, model, gvw, year) 
VALUES
('4585 AW','ALBION','RIEVER',20321,1963, ' ');

So I temporally deleted the body data and then give the error

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year) VALUES('4585 AW','ALBION','RIEVER',20321,1963)
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "YEAR": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I have columns both called year and body, yet I'm getting errors. How can I fix this?

回答1:

Seems like you specified the column names under quotes in your DDL.

Oracle considers identifiers under quotes as a case sensitive, so creating a table with a column name as "body" or "year" will not resolve body or year in your query, because when you omit the quotes, it considers the upper case variant.

So when you have this:

 create table driver_case (registration varchar2(60), 
                      make varchar2(60),
                      model varchar2(60),
                      gvw number,
                      "year" number, 
                      "body" varchar2(60));

and then try to do

select year, body from driver_case

then Oracle tries to find column "YEAR", "BODY" (remember, without an identifier without quotes gets converted to upper case) which is not the same as "year", "body" in your table.

Your solutions?

• Don't mention column names under quotes in the DDL
• If you disregard the above point, then you must mention the column names under quotes in all your DML statements.

I demonstrate the above point in this SQL Fiddle

回答2:

Try this:

INSERT
    WHEN city='palanpur' THEN INTO Ashish 
    When city='Aburoad' THEN INTO abhi
SELECT no, name
    FROM arpit;