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!

invalid Identifier

Discussion in 'SQL PL/SQL' started by rajchoopra, Oct 1, 2012.

  1. rajchoopra

    rajchoopra Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi all,

    I created a new tabel (" billtwo") by using a select into command to copy some of selected columns and data by using a old table ("contact").

    here is the query i used

    CREATE TABLE BILLTWO AS SELECT member_id,contact_type,organization_id,
    from contact where contact_type = 10 and access_type = 10

    Now i need to add few more columns from another table("organization") into billtwo table,where organization_id exists in both tables so by this colum i need to copy few more columns

    the query i used is below :

    INSERT INTO billtwo(
    ORGANIZATION_name,
    ein)
    SELECT
    ORGANIZATION_NAME,EIN
    FROM ORGANIZATION
    WHERE ORGANIZATION.ORGANIZATION_ID = billtwo_ORGANIZATION_ID

    On executio of the above quey i get this error
    ERROR at line 10:
    ORA-00904: "BILLTWO"."ORGANIZATION_ID": invalid
    identifier
    ::confused:

    please guide me o trouble shoot this error
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Raj,

    In this "billtwo" table you don't have that column called organization_id. You need to alter your table to add that column. My instance was down to give you an example. Just refer to the following link to how to add column to your tables
    Link1

    Its throwing error just because that field "ORGANIZATION_ID" is not there in that table.
     
  3. rajchoopra

    rajchoopra Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi Bharat,

    thanks for reply,

    BILLTWO table has the column organization_id, i reched it just now.
     
    Bharat likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Certainly it does, as your create table statement selected it (of course the statement you posted won't work as it has an extra comma and will throw an error):

    Code (SQL):
    SQL> CREATE TABLE contact(member_id NUMBER, contact_type NUMBER, organization_id varchar2(6), access_type NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..50 loop
    3 INSERT INTO contact
    4 VALUES(i, 10, 'ORG'||i, 10);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE TABLE organization(ein NUMBER, organization_id varchar2(6), organization_name varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..50 loop
    3 INSERT INTO organization
    4 VALUES(i*5000, 'ORG'||i, 'Flarpnorfer Budwump '||i);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE TABLE BILLTWO AS SELECT member_id,contact_type,organization_id,
    2 FROM contact WHERE contact_type = 10 AND access_type = 10;
    FROM contact WHERE contact_type = 10 AND access_type = 10
    *
    ERROR at line 2:
    ORA-00936: missing expression
     
    SQL>
    SQL> SELECT * FROM billtwo;
    SELECT * FROM billtwo
    *
    ERROR at line 1:
    ORA-00942: TABLE OR VIEW does NOT exist
     
    SQL>
    SQL> CREATE TABLE BILLTWO AS SELECT member_id,contact_type,organization_id
    2 FROM contact WHERE contact_type = 10 AND access_type = 10;
     
    TABLE created.
     
    SQL>
    SQL> SELECT * FROM billtwo;
     
    MEMBER_ID CONTACT_TYPE ORGANI
    ---------- ------------ ------
    1 10 ORG1
    2 10 ORG2
    3 10 ORG3
    4 10 ORG4
    5 10 ORG5
    6 10 ORG6
    7 10 ORG7
    8 10 ORG8
    9 10 ORG9
    10 10 ORG10
    11 10 ORG11
    12 10 ORG12
    13 10 ORG13
    14 10 ORG14
    15 10 ORG15
    16 10 ORG16
    17 10 ORG17
    18 10 ORG18
    19 10 ORG19
    20 10 ORG20
    21 10 ORG21
    22 10 ORG22
    23 10 ORG23
    24 10 ORG24
    25 10 ORG25
    26 10 ORG26
    27 10 ORG27
    28 10 ORG28
    29 10 ORG29
    30 10 ORG30
    31 10 ORG31
    32 10 ORG32
    33 10 ORG33
    34 10 ORG34
    35 10 ORG35
    36 10 ORG36
    37 10 ORG37
    38 10 ORG38
    39 10 ORG39
    40 10 ORG40
    41 10 ORG41
    42 10 ORG42
    43 10 ORG43
    44 10 ORG44
    45 10 ORG45
    46 10 ORG46
    47 10 ORG47
    48 10 ORG48
    49 10 ORG49
    50 10 ORG50
     
    50 ROWS selected.
     
    SQL>
    SQL> ALTER TABLE billtwo ADD organization_name varchar2(40);
     
    TABLE altered.
     
    SQL> ALTER TABLE billtwo ADD ein NUMBER;
     
    TABLE altered.
     
    SQL>
    SQL> INSERT INTO billtwo(
    2 organization_name,
    3 ein)
    4 SELECT
    5 organization.organization_name,organization.ein
    6 FROM organization, billtwo
    7 WHERE organization.organization_id = billtwo.organization_id;
     
    50 ROWS created.
     
    SQL>
    SQL> SELECT * FROM billtwo;
     
    MEMBER_ID CONTACT_TYPE ORGANI ORGANIZATION_NAME EIN
    ---------- ------------ ------ ---------------------------------------- ----------
    1 10 ORG1
    2 10 ORG2
    3 10 ORG3
    4 10 ORG4
    5 10 ORG5
    6 10 ORG6
    7 10 ORG7
    8 10 ORG8
    9 10 ORG9
    10 10 ORG10
    11 10 ORG11
    12 10 ORG12
    13 10 ORG13
    14 10 ORG14
    15 10 ORG15
    16 10 ORG16
    17 10 ORG17
    18 10 ORG18
    19 10 ORG19
    20 10 ORG20
    21 10 ORG21
    22 10 ORG22
    23 10 ORG23
    24 10 ORG24
    25 10 ORG25
    26 10 ORG26
    27 10 ORG27
    28 10 ORG28
    29 10 ORG29
    30 10 ORG30
    31 10 ORG31
    32 10 ORG32
    33 10 ORG33
    34 10 ORG34
    35 10 ORG35
    36 10 ORG36
    37 10 ORG37
    38 10 ORG38
    39 10 ORG39
    40 10 ORG40
    41 10 ORG41
    42 10 ORG42
    43 10 ORG43
    44 10 ORG44
    45 10 ORG45
    46 10 ORG46
    47 10 ORG47
    48 10 ORG48
    49 10 ORG49
    50 10 ORG50
    Flarpnorfer Budwump 1 5000
    Flarpnorfer Budwump 2 10000
    Flarpnorfer Budwump 3 15000
    Flarpnorfer Budwump 4 20000
    Flarpnorfer Budwump 5 25000
    Flarpnorfer Budwump 6 30000
    Flarpnorfer Budwump 7 35000
    Flarpnorfer Budwump 8 40000
    Flarpnorfer Budwump 9 45000
    Flarpnorfer Budwump 10 50000
    Flarpnorfer Budwump 11 55000
    Flarpnorfer Budwump 12 60000
    Flarpnorfer Budwump 13 65000
    Flarpnorfer Budwump 14 70000
    Flarpnorfer Budwump 15 75000
    Flarpnorfer Budwump 16 80000
    Flarpnorfer Budwump 17 85000
    Flarpnorfer Budwump 18 90000
    Flarpnorfer Budwump 19 95000
    Flarpnorfer Budwump 20 100000
    Flarpnorfer Budwump 21 105000
    Flarpnorfer Budwump 22 110000
    Flarpnorfer Budwump 23 115000
    Flarpnorfer Budwump 24 120000
    Flarpnorfer Budwump 25 125000
    Flarpnorfer Budwump 26 130000
    Flarpnorfer Budwump 27 135000
    Flarpnorfer Budwump 28 140000
    Flarpnorfer Budwump 29 145000
    Flarpnorfer Budwump 30 150000
    Flarpnorfer Budwump 31 155000
    Flarpnorfer Budwump 32 160000
    Flarpnorfer Budwump 33 165000
    Flarpnorfer Budwump 34 170000
    Flarpnorfer Budwump 35 175000
    Flarpnorfer Budwump 36 180000
    Flarpnorfer Budwump 37 185000
    Flarpnorfer Budwump 38 190000
    Flarpnorfer Budwump 39 195000
    Flarpnorfer Budwump 40 200000
    Flarpnorfer Budwump 41 205000
    Flarpnorfer Budwump 42 210000
    Flarpnorfer Budwump 43 215000
    Flarpnorfer Budwump 44 220000
    Flarpnorfer Budwump 45 225000
    Flarpnorfer Budwump 46 230000
    Flarpnorfer Budwump 47 235000
    Flarpnorfer Budwump 48 240000
    Flarpnorfer Budwump 49 245000
    Flarpnorfer Budwump 50 250000
     
    100 ROWS selected.
     
    SQL>
    SQL> ROLLBACK;
     
    ROLLBACK complete.
     
    SQL>
    SQL> UPDATE billtwo b2
    2 SET b2.organization_name = (SELECT o.organization_name FROM organization o WHERE o.organization_id = b2.organization_id),
    3 b2.ein = (SELECT o.ein FROM organization o WHERE o.organization_id = b2.organization_id);
     
    50 ROWS updated.
     
    SQL>
    SQL> SELECT * FROM billtwo;
     
    MEMBER_ID CONTACT_TYPE ORGANI ORGANIZATION_NAME EIN
    ---------- ------------ ------ ---------------------------------------- ----------
    1 10 ORG1 Flarpnorfer Budwump 1 5000
    2 10 ORG2 Flarpnorfer Budwump 2 10000
    3 10 ORG3 Flarpnorfer Budwump 3 15000
    4 10 ORG4 Flarpnorfer Budwump 4 20000
    5 10 ORG5 Flarpnorfer Budwump 5 25000
    6 10 ORG6 Flarpnorfer Budwump 6 30000
    7 10 ORG7 Flarpnorfer Budwump 7 35000
    8 10 ORG8 Flarpnorfer Budwump 8 40000
    9 10 ORG9 Flarpnorfer Budwump 9 45000
    10 10 ORG10 Flarpnorfer Budwump 10 50000
    11 10 ORG11 Flarpnorfer Budwump 11 55000
    12 10 ORG12 Flarpnorfer Budwump 12 60000
    13 10 ORG13 Flarpnorfer Budwump 13 65000
    14 10 ORG14 Flarpnorfer Budwump 14 70000
    15 10 ORG15 Flarpnorfer Budwump 15 75000
    16 10 ORG16 Flarpnorfer Budwump 16 80000
    17 10 ORG17 Flarpnorfer Budwump 17 85000
    18 10 ORG18 Flarpnorfer Budwump 18 90000
    19 10 ORG19 Flarpnorfer Budwump 19 95000
    20 10 ORG20 Flarpnorfer Budwump 20 100000
    21 10 ORG21 Flarpnorfer Budwump 21 105000
    22 10 ORG22 Flarpnorfer Budwump 22 110000
    23 10 ORG23 Flarpnorfer Budwump 23 115000
    24 10 ORG24 Flarpnorfer Budwump 24 120000
    25 10 ORG25 Flarpnorfer Budwump 25 125000
    26 10 ORG26 Flarpnorfer Budwump 26 130000
    27 10 ORG27 Flarpnorfer Budwump 27 135000
    28 10 ORG28 Flarpnorfer Budwump 28 140000
    29 10 ORG29 Flarpnorfer Budwump 29 145000
    30 10 ORG30 Flarpnorfer Budwump 30 150000
    31 10 ORG31 Flarpnorfer Budwump 31 155000
    32 10 ORG32 Flarpnorfer Budwump 32 160000
    33 10 ORG33 Flarpnorfer Budwump 33 165000
    34 10 ORG34 Flarpnorfer Budwump 34 170000
    35 10 ORG35 Flarpnorfer Budwump 35 175000
    36 10 ORG36 Flarpnorfer Budwump 36 180000
    37 10 ORG37 Flarpnorfer Budwump 37 185000
    38 10 ORG38 Flarpnorfer Budwump 38 190000
    39 10 ORG39 Flarpnorfer Budwump 39 195000
    40 10 ORG40 Flarpnorfer Budwump 40 200000
    41 10 ORG41 Flarpnorfer Budwump 41 205000
    42 10 ORG42 Flarpnorfer Budwump 42 210000
    43 10 ORG43 Flarpnorfer Budwump 43 215000
    44 10 ORG44 Flarpnorfer Budwump 44 220000
    45 10 ORG45 Flarpnorfer Budwump 45 225000
    46 10 ORG46 Flarpnorfer Budwump 46 230000
    47 10 ORG47 Flarpnorfer Budwump 47 235000
    48 10 ORG48 Flarpnorfer Budwump 48 240000
    49 10 ORG49 Flarpnorfer Budwump 49 245000
    50 10 ORG50 Flarpnorfer Budwump 50 250000
     
    50 ROWS selected.
     
    SQL>
    You have two problems with your approach --

    1) You can't reference a column from a table you do not have in a JOIN (this is why you received the error you did).
    2) You do not want to INSERT rows into a table to modify existing data; you want to update the table to populate the new columns.

    You did not alter your billtwo table to include the new columns so inserting/updating data would be impossible; notice I added the columns before I began the second insert/update process. Notice also that your INSERT, when properly coded, ADDS 50 rows to the table (given that the original tables have 50 rows each) rather than modifying the existing 50 rows. I have coded the proper update statement in my example. Both columns could be added with a single statement but I wanted to show the process of adding columns more than once.
     
    rajchoopra likes this.
  5. rajchoopra

    rajchoopra Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    thanks for reply, the problem was solved.
    i used alter cmd to create the new columns and update cmd to insert the new data.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thats great, identifying your problem and solving it, really develops your skills. Keep it up.
     
    rajchoopra likes this.