1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00984: column not allowed here

Discussion in 'SQL PL/SQL' started by nighthunter, Jan 14, 2012.

  1. nighthunter

    nighthunter Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    CREATE TABLE vb_cititori(
    index_cititor NUMBER(6),
    nume_cititor VARCHAR2(20) not null,
    legit_nr NUMBER(6) not null,
    legit_elib DATE default SYSDATE,
    legit_valab DATE default legit_elib+24luni,
    nast_data DATE,
    nast_loc VARCHAR2(20),
    act_seria VARCHAR2(2),
    act_nr NUMBER(8),
    act_autorit VARCHAR2(20),
    act_dataelib DATE,
    CONSTRAINT vb_cititori_pk_index_cititor PRIMARY KEY (index_cititor),
    CONSTRAINT vb_cititori_uk_nume_cititor UNIQUE (nume_cititor),
    CONSTRAINT vb_cititori_uk_legit_nr UNIQUE (legit_nr),
    );
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I see a Create Table statement, but what are you trying to do. Where is the error coming?

    BTW there is an extra comma on the last CONSTRAINT.
     
  3. nighthunter

    nighthunter Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I have to do a project for school to create some tables an do some operations on them ..... i created first table , but on this one i have that error .... ive noticed the extra comma on the last CONSTRAINT but even if i delete that comma still have the same error
     
  4. nighthunter

    nighthunter Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    its in romaniam al i wrote there ....i translate in english for you ?? maybe its easier to find the error
     
  5. nighthunter

    nighthunter Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    ORA-00904: : invalid identifier if i erase that comma
     
  6. nighthunter

    nighthunter Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    sql help

    have to create this table

    CITITORI
    Index reader N 6 PK
    Name C 20 NotNull, UK
    nr legit N 6 NotNull, UK
    release date acces card D default sysdate
    Valability acces card D default date of release date legit+ 24 months
    birth date D
    Birth place C 20


    the problem is that i know how to create all the collumns in table but i dont know how to do this collumn
    Valability acces card D default date of release date acces card+ 24 months
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: sql help


    You might need to use Trigger for this purpose.

    Code (SQL):

    SQL> CREATE TABLE "CITITORI"
      2    (
      3      "INDEX_READER" NUMBER(6,0),
      4      "NAME"         VARCHAR2(20 BYTE) NOT NULL ENABLE,
      5      "NR_LEGIT"     NUMBER(6,0) NOT NULL ENABLE,
      6      "RELEASE_DATE_ACCES_CARD" DATE DEFAULT SYSDATE,
      7      "VALABILITY_ACCES_CARD" DATE ,
      8      "BIRTH_DATE" DATE,
      9      "BIRTH_PLACE" VARCHAR2(20 BYTE),
     10      PRIMARY KEY ("INDEX_READER") ,
     11      UNIQUE ("NAME") ,
     12      UNIQUE ("NR_LEGIT")
     13    );

    TABLE created.

    SQL> CREATE OR REPLACE TRIGGER "TRIGGER1"
      2  BEFORE INSERT ON CITITORI
      3  REFERENCING OLD AS O NEW AS N
      4  FOR EACH ROW
      5  WHEN (n.VALABILITY_ACCES_CARD IS NULL AND n.RELEASE_DATE_ACCES_CARD IS NOT NULL )
      6  BEGIN
      7  :n.VALABILITY_ACCES_CARD := ADD_MONTHS(:n.RELEASE_DATE_ACCES_CARD,24) ;
      8  END;
      9  /

    TRIGGER created.

    SQL> INSERT INTO CITITORI (INDEX_READER,NAME,NR_LEGIT,BIRTH_DATE,BIRTH_PLACE)
      2  VALUES ( 1,'RAJ',1,TO_DATE('01011980','DDMMYYYY'),'INDIA');

    1 ROW created.

    SQL> INSERT INTO CITITORI (INDEX_READER,NAME,NR_LEGIT,BIRTH_DATE,BIRTH_PLACE,RELEASE_DATE_ACCES_CARD)
      2  VALUES ( 2,'RAJU',2,TO_DATE('01011981','DDMMYYYY'),'INDIA',TO_DATE('01012011','DDMMYYYY'));

    1 ROW created.

    SQL> INSERT INTO CITITORI (INDEX_READER,NAME,NR_LEGIT,BIRTH_DATE,BIRTH_PLACE,VALABILITY_ACCES_CARD)
      2  VALUES ( 3,'RAJUVA',3,TO_DATE('01011981','DDMMYYYY'),'INDIA',TO_DATE('31122011','DDMMYYYY'));

    1 ROW created.

    SQL> INSERT INTO CITITORI (INDEX_READER,NAME,NR_LEGIT,BIRTH_DATE,BIRTH_PLACE,RELEASE_DATE_ACCES_CARD,VALABILITY_ACCES_CARD)
      2  VALUES ( 4,'RAJUVAN',4,TO_DATE('01011981','DDMMYYYY'),'INDIA',TO_DATE('01012011','DDMMYYYY'),TO_DATE('31122011','DDMMYYYY'));

    1 ROW created.

    SQL> SELECT * FROM CITITORI;

    INDEX_READER NAME         NR_LEGIT RELEASE_D VALABILIT BIRTH_DAT BIRTH_PLACE
    ------------ ---------- ---------- --------- --------- --------- --------------

               1 RAJ                 1 16-JAN-12 16-JAN-14 01-JAN-80 INDIA
               2 RAJU                2 01-JAN-11 01-JAN-13 01-JAN-81 INDIA
               3 RAJUVA              3 16-JAN-12 31-DEC-11 01-JAN-81 INDIA
               4 RAJUVAN             4 01-JAN-11 31-DEC-11 01-JAN-81 INDIA

    SQL>