CODEWITHSUNDEEP
oracle-databaseddl
Kevin Tan
Kevin Tan

Reputation: 11

SQL invalid identifier 00904. 00000 - "%s: invalid identifier"

Calendar

CREATE TABLE CALENDAR
(
    CalendarKey int NOT NULL PRIMARY KEY,
    Date DATE DEFAULT (SYSDATE), 
    DayofWeek VARCHAR2(50) NOT NULL, 
    DayofMonth VARCHAR2(50) NOT NULL,
    DayofYear VARCHAR2(50) NOT NULL,
    WeekofMonth VARCHAR2(50) NOT NULL,
    WeekofYear VARCHAR2(50) NOT NULL,
    Month MONTH DEFAULT (SYSMONTH),
    Year YEAR DEFAULT (SYSYEAR)
);

This code causes an error:

enter image description here

Error report -
ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Don't see any error, please help.

Upvotes: 1

Views: 1619

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

SQL> create table calendar
  2    (calendarkey int constraint pk_cal primary key,
  3     datum       date default (sysdate),
  4     dayofweek   varchar2(50) not null,
  5     dayofmonth  varchar2(50) not null,
  6     dayofyear   varchar2(50) not null,
  7     weekofmonth varchar2(50) not null,
  8     weekofyear  varchar2(50) not null,
  9     month       number(2) default extract (month from sysdate),
 10     year        number(4) default extract (year  from sysdate)
 11  );

Table created.

SQL>

So, what did you do wrong (along with additional notes):

  • you can't name a column date as it is reserved for datatype name (actually, you could, but you should not - by enclosing its name into double quotes)
  • primary key implies that the column can't contain NULL values, so - no need to specify NOT NULL. Also, it is a good habit to name all constraints
  • there aren't sysmonth nor sysyear in Oracle, but - if I got it right - you can use sysdate and extract those values (month and year) from it
  • do you really need 50 characters to store days and weeks? Not that you're charged on column size, but ... which day occupies up to 50 characters?

Upvotes: 1

Related Questions