SQL invalid identifier 00904. 00000 - “%s: invalid identifier”

Question

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.

There is no sysmonth or sysyear in Oracle. Where in the manual did you find that? — a_horse_with_no_name
In addition to the other comments, why separate columns for 'month' and 'year'? That's sure to cause problems down the road. Just use a single column of type DATE. If all you care about is the month and year, you can just use TRUNC when you query the column, to ignore the day and time parts of DATE. — EdStevens

Littlefoot Littlefoot · Accepted Answer · 2020-10-06T18:35:24

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?

SQL invalid identifier 00904. 00000 - “%s: invalid identifier”

1 Answers